ibm/db2-samples

Db2 application code, configuration samples, and other examples

This repo contains many sets of sample application code, API usage examples, configuration examples, etc. that are organized into sub-folders according to programming language or sample topic. Some folders contain multiple samples.

Not all sample programs are available on all platforms or supported programming languages. You can use the sample programs as templates to create your own applications, and as a learning tool to understand Db2 functionality.

Db2 sample programs are provided "as is" without any warranty of any kind. The user, and not IBM, assumes the entire risk of quality, performance, and repair of any defects. Contributions to this open-source project are welcomed, see CONTRIBUTING.

Besides the sample application program files, other sample files are also provided in this repo. These include build files and makefiles to compile and link the sample programs, error-checking utility files which are linked in to most sample programs, and various script files to assist in application development. For example, scripts are provided to catalog and uncatalog stored procedures and UDFs in several language sub-directories. Each samples directory has a README file which describes the files contained in the directory.

The following table shows the sample directories and README files for the main supported programming languages and APIs. You can also access the sample files in the listed samples directories. For the directory paths, the UNIX-style slashes are used, as in samples/c, except where the directory is for Windows only, as in samples\VB\ADO.

Sample Details By Language

C	README	SOURCE
C++	README	SOURCE
CLI	README	SOURCE
CLP	README	SOURCE
Micro Focus COBOL	README	SOURCE
JDBC	README	SOURCE
SQLJ	README	SOURCE
Perl	README	SOURCE
PHP	README	SOURCE
SQL Procedures	README	SOURCE
XML	README	SOURCE

Note:

There are various samples provided for demonstrating Native XML support in Db2. The XML samples come with their own build files, utility files, supporting scripts etc. XQuery samples are all further collected in a separate directory under the parent XML directory. The XQuery as well as other samples demonstrating administrative and application development support are available in various languages.

Sample program file extensions differ for each supported language, and for embedded SQL and non-embedded SQL programs within each language. File extensions might also differ for groups of programs within a language. These different sample file extensions are categorized in the following tables:

Sample file extensions by language

Language	Directory	Embedded SQL programs	Non-embedded SQL programs
C	samples/c samples/cli (CLI programs) samples/xml/c samples/xml/xquery/c samples/xml/cli (CLI programs) samples/xml/xquery/cli (CLI programs)	.sqc	.c
C++	samples/cpp	.sqC (UNIX) .sqx (Windows) .C (UNIX) .cxx (Windows)
C#	samples\.NET\cs		.cs
COBOL	samples/cobol	samples/cobol_mf	.sqb
Java	samples/java/jdbc samples/java/sqlj samples/xml/java/jdbc samples/xml/xquery/java/jdbc samples/xml/java/sqlj samples/xml/xquery/java/sqlj samples/java/WebSphere samples/java/plugin	.sqlj	.java
Perl	samples/perl	.pl	.pm
PHP	samples/php	.php
REXX	samples/rexx	.cmd	.cmd
Visual Basic	samples\VB\ADO samples\VB\MTS samples\VB\RDO		.bas .frm .vbp
Visual Basic .NET	samples\.NET\vb		.vb
Visual C++	samples\VC\ADO		.cpp .dsp .dsw

Sample file extensions by program group

Sample group	Directory	File Extension
CLP	samples/clp samples/xml/clp samples/xml/xquery/clp	.db2
OLE	samples\ole\msvb (Visual Basic) samples\ole\msvc (Visual C++)	.bas .vbp (Visual Basic) .cpp (Visual C++)
OLE DB	samples\oledb	.db2
SQLPL	samples/sqlpl	.db2 (SQL Procedure scripts) .c (CLI Client Applications) .sqc (embedded C Client Applications) .java (JDBC Client Applications)
User exit	samples/c	.ctsm (UNIX & Windows) .cdisk (UNIX & Windows) .ctape (UNIX) .cxbsa (UNIX)

Notes

Directory delimiters

The directory delimiter on UNIX is a /. On Windows it is a \. In the tables, the UNIX delimiters are used unless the directory is only available on Windows.

Embedded SQL programs

Require precompilation, except for REXX embedded SQL programs where the embedded SQL statements are interpreted when the program is run.

IBM COBOL samples

Are only supplied for AIX and Windows 32-bit operating systems in the cobol subdirectory.

Micro Focus COBOL samples

Are only supplied for AIX, Linux, and Windows 32-bit operating systems in the cobol_mf subdirectory.

Java samples

Are Java Database Connectivity (JDBC) applets, applications, and routines, embedded SQL for Java (SQLJ) applets, applications, and routines. Available for demonstrating native XML support as well. Java samples are available on all supported Db2 platforms.

REXX samples

Are only supplied for AIX and Windows 32-bit operating systems.

CLP samples

Are Command Line Processor scripts that execute SQL statements. Also available to show XML and XQuery support.

OLE samples

Are for Object Linking and Embedding (OLE) in Microsoft Visual Basic and Microsoft Visual C++, supplied for Windows operating systems only.

Visual Basic samples

Are ActiveX Data Objects, Remote Data Objects, and Microsoft Transaction Server samples, supplied on Windows operating systems only.

Visual C++ samples

Are ActiveX Data Object samples, supplied on Windows operating systems only.

User exit samples

Are Log Management User Exit programs used to archive and retrieve database log files. The files must be renamed with a .c extension and compiled as C language programs.

Structure and design

Most of the Db2 samples in C, CLI, C++, C#, Java, Perl, PHP, Visual Basic ADO, and Visual Basic .NET are organized to reflect an object-based design model of the database components.. The samples are grouped in categories representing different levels of Db2. The level to which a sample belongs is indicated by a two character prefix at the beginning of the sample name. Not all levels are represented in the samples for each Application Programming Interface, but for the samples as a whole, the levels are represented as follows:

prefix

Db2 Level

il

Installation Image Level

cl

Client Level

in

Instance Level

db

Database Level

ts

Table Space Level

tb

Table Level

dt

Data Type Level

The levels show a hierarchical structure. The Installation image level is the top level of Db2. Below this level, a client-level application can access different instances; an instance can have one or more databases; a database has table spaces within which tables exist, and which in turn hold data of different data types.

This design does not include all Db2 samples. The purpose of some samples is to demonstrate different methods for accessing data. These methods are the main purpose of these samples so they are represented by these methods in a similar manner as mentioned previously:

prefix

Programming method

fn

SQL function

sp

Stored procedure

ud

User-defined function

There are other samples not included in this design, such as the XML samples, Log Management User Exit samples, samples in COBOL, Visual C++, REXX, Object Linking and Embedding (OLE) samples, CLP scripts, and SQL Procedures. XML samples are broadly categorized into samples demonstrating native XML administration, application development and XQuery support.

db2-samples's Issues

Asking about query's restriction (over 32000 byte) about "wlmhist.pl" that is sample perl script where is located in $HOME/sqllib/samples/perl.

Hello, team.
i'm SeokHwan choi from db2 support team. i'm posting to ask "wlmhist.pl" which is sample perl script where is located in $HOME/sqllib/samples/perl.

[Question]
Q1) Could you please make sure if the "STMT_TEXT" column of "ACTIVITYSTMT_DB2ACTIVITIES" can store long-query that is over 32000 characters?

Q2) How to do extract long-query that is over 32000 characters from "STMT_TEXT" column of "ACTIVITYSTMT_DB2ACTIVITIES" ?

Q3) How to do insert long-query that is over 32000 characters to WLMHIST table ?

Q4) we tried to reproduce a similar issue via sample Perl. L3 team got a message like Error: Data in column 0 has been truncated to 32700 bytes". So, could you please make sure if "Error: Data in column 0 has been truncated to 32700 bytes" is related to DB29320W?
"DB29320W Output has been truncated."

Q5) Is there any restriction that is related to query length from the perl? e.g, it can't retrieve over 32000?

Q6) if customer has a problem in terms of sample code where located in $HOME/sqllib/samples/, should customer ask via this github page? or is it correct way to open DB2 SF case?

Note that, I've been handling DB2 case "TS009058089" about this case.

Background history
1)Customer has been using wlmhist.pl" that is sample perl script where is located in $HOME/sqllib/samples/perl. when customer tried to retrived full sqls that worked for business purposes. it has been truncated.
in short, customer wasn't able to get full sql. ( from this script, there is variable "$sqlToGetStmt" to extract various informations including "stmt_text" via activity event monitor.)

"select **a2.stmt_text,** a1.time_created, a1.time_started, ".
"a1.time_completed, a1.activity_id, a1.uow_id, a1.appl_id, ".
"a1.activity_secondary_id, ".
"a1.activity_type, a1.session_auth_id, a2.comp_env_desc ".
"from $activity as a1, $activityStmt as a2 ".
"where a1.activity_id = a2.activity_id and a1.uow_id = a2.uow_id and ".
"a1.appl_id = a2.appl_id and ".
"a1.activity_secondary_id = a2.activity_secondary_id and ".
"$checkPartitionText".
"partial_record = 0 and ".
"appl_name != 'DB2HMON' and ".
"(a1.activity_type = 'DML' or a1.activity_type = 'READ_DML' or ".
"a1.activity_type = 'WRITE_DML') ".
"$fromTimeText $toTimeText $workloadIdText $serviceClassText ".
"$serviceSubClassText";

2)"STMT_TEXT" has been setup as CLOB, and db2 moniotor L3 team said STMT_TEXT can store statements bigger than 32000 characters. The event monitor can accommodate statements up to 2Gb in size. however, it has truncated full SQL when perl has handled SQL.
when L3 team tried to reproduce via simple way, it has returned message like ""Error: Data in column 0 has been truncated to 32700 bytes" for SQL that length was 8166.

db2 "select length(stmt_text) from ACTIVITYSTMT_ACT"

1
-----------
        268
       8873
        584
      81661

  4 record(s) selected.

$ db2 describe table ACTIVITYSTMT_DB2ACTIVITIES

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PARTITION_KEY                   SYSIBM    INTEGER                      4     0 No
ACTIVATE_TIMESTAMP              SYSIBM    TIMESTAMP                   10     6 No
ACTIVITY_ID                     SYSIBM    BIGINT                       8     0 No
ACTIVITY_SECONDARY_ID           SYSIBM    SMALLINT                     2     0 No
APPL_ID                         SYSIBM    VARCHAR                     64     0 No
COMP_ENV_DESC                   SYSIBM    BLOB                     10240     0 No
CREATOR                         SYSIBM    VARCHAR                    128     0 No
EFF_STMT_TEXT                   SYSIBM    CLOB                   2097152     0 Yes
EXECUTABLE_ID                   SYSIBM    VARCHAR                     32     0 No
NUM_ROUTINES                    SYSIBM    INTEGER                      4     0 No
PACKAGE_NAME                    SYSIBM    VARCHAR                    128     0 No
PACKAGE_VERSION_ID              SYSIBM    VARCHAR                     64     0 No
PARTITION_NUMBER                SYSIBM    SMALLINT                     2     0 No
PLANID                          SYSIBM    BIGINT                       8     0 No
ROUTINE_ID                      SYSIBM    BIGINT                       8     0 No
SECTION_ENV                     SYSIBM    BLOB                 140509184     0 No
SECTION_NUMBER                  SYSIBM    BIGINT                       8     0 No
SEMANTIC_ENV_ID                 SYSIBM    BIGINT                       8     0 No
STMT_FIRST_USE_TIME             SYSIBM    TIMESTAMP                   10     6 No
STMT_INVOCATION_ID              SYSIBM    BIGINT                       8     0 No
STMT_ISOLATION                  SYSIBM    BIGINT                       8     0 No
STMT_LAST_USE_TIME              SYSIBM    TIMESTAMP                   10     6 No
STMT_LOCK_TIMEOUT               SYSIBM    INTEGER                      4     0 No
STMT_NEST_LEVEL                 SYSIBM    BIGINT                       8     0 No
STMT_PKGCACHE_ID                SYSIBM    BIGINT                       8     0 No
STMT_QUERY_ID                   SYSIBM    BIGINT                       8     0 No
STMT_SOURCE_ID                  SYSIBM    BIGINT                       8     0 No
STMT_TEXT                       SYSIBM    CLOB                   2097152     0 Yes
STMT_TYPE                       SYSIBM    BIGINT                       8     0 No
STMTID                          SYSIBM    BIGINT                       8     0 No
STMTNO                          SYSIBM    INTEGER                      4     0 No
UOW_ID                          SYSIBM    INTEGER                      4     0 No

3)Usually, customer has been using "wlmhist.pl" via the purpose below.

1.select long query that is over 32000 characters from activitystmt_db2activies
2.insert query that extracted in No,1 to WLHHIST table

in this case, customer said it looks like either 1) the long query that is over 32000 characters was truncated when it tried to select "STMT_TEXT column of ACTIVITYSTMT_DB2ACTIVITIES" or 2) there was a problem when it tried to insert WLMHIST table.2.insert query that extracted in No,1 to WLHHIST table

Currently, customer has added new line under "my $sqlToGetStmt" like 'activity.jpg'

[21:kr007491@ecurep]:/ecurep/sf/TS009/058/TS009058089/mail20220421-084547-sety $ pwd
/ecurep/sf/TS009/058/TS009058089/mail20220421-084547-sety
[21:kr007491@ecurep]:/ecurep/sf/TS009/058/TS009058089/mail20220421-084547-sety $ ls -al
total 20484
drwxrws--- 2 root swsupt 4096 Apr 21 08:46 .
drwx--S--- 19 root swsupt 4096 Apr 29 01:51 ..
-rw-r----- 1 root swsupt 6222 Apr 21 08:46 MAILINFO.wri
-rw-r----- 1 root swsupt 5136280 Apr 21 08:46 activity.jpg <<---
-rw-r----- 1 root swsupt 27246 Apr 21 08:46 mail.html
-rw-r----- 1 root swsupt 1152 Apr 21 08:46 mailimg.1.gif

4)I see that the perl code uses DBI for its SQL capability:

**use DBI;**

I cannot comment on how the perl script can result in DB29320W.

Best regards,

SH.

Group sample by type of Db2

Even all these samples are for Db2 (previously for LUW), not all example run in the current different flavors of Db2. For example, the current pull request "Added demo for external model deployment via Python UDF. Also reorganized files for easier navigation" is just for Db2 Warehouse, it does not run in other Db2, because external UDF written in python are not supported in all editions.
With this in mind, I propose to put tags to indicate where each sample can be executed. Or something to group the sample by Db2 type.

IBM DB2 is giving wrong database against db2inst2 if there two instances are present on the DB2 11.5

Hello, I have a DB2 system that has two instances. After loading /opt/ibm/db2/V11.5/lib64/libdb2.so, I use db2DbDirOpenScan followed by db2DbDirGetNextEntry to get the database against each instance. An example is shown below.

db2inst1 -> DB1, DB2 database names

db2inst2 -> IDB1, IDB2 database names

We always get database "DB1, DB2" against db2inst2, which is incorrect.

When we tested the aforementioned method on db2 version 11.1, it worked well. It is not compatible with db2 version 11.5.

Do we have any idea what the problem could be with 11.5 or if something has changed in db2 11.5 to retrieve a list of databases in relation to db2DbDirOpenScan and db2DbDirGetNextEntry?

NameError: name 'pandas' is not defined

When running this notebook on my python 3.7 environment, I encountered the following error

---> 14 wine = pandas.read_sql(sql,hdbi) .. NameError: name 'pandas' is not defined

Changing the relevant lines like below, it worked fine.
#wine = pandas.read_sql(sql,hdbi) wine = pd.read_sql(sql,hdbi)

I guess that is because we declared in the start of code
import pandas as pd
Posting this just in case we need to change it.

Restore archive logs only

Is it possible to restore only archive logs that are not part of the online backup image?
E.g

Today some logs got archived in the case of db2. I never took the online backup. The next day, I tried to restore only the archive log which was archived the day before.

DB2 API is returning error -2546

I am trying to perform redirected restore and restore API is returning sqlcode -2546 on the AIX 7.2 environment. I am passing db2 version 11.1.0.0 to the API while restoring. I am running on db2 version DB2 v11.1.1.1.

I am not sure why restore API is returning -2546 error on the AIX environment. Also, I could not see what is error code -2546. Can you please help me to resolve this?

FUNCTION: DB2 UDB, database utilities, sqludMRResponse, probe:1136
DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
-2546
DATA #2 : Hexdump, 0 bytes
Object not dumped: Address: 0x0A0002001AC6E6B0 Size: 0 Reason: Zero-length data

How to add changes for NO ENCRYPT option

I am trying to restore encrypted DB to non-encryped DB. I made changes by setting piDbEncOpts to SQL_ENCRYPT_DB_NO but still restore is being failed. Is there db2 sample code is there where I can check how to set "NO Encrypt" option in DB2.

db2mon script throwing error in Ubuntu 20.4 intermiddently

/home/db2inst1/sqllib/samples/perf/db2mon.sh: 49: [: 60: unexpected operator

https://github.com/IBM/db2-samples/blob/master/perf/db2mon.sh

DB2 C API to backup and restore only transaction logs

With IBM DB2, is it possible to take only transaction logs backup and restore from C API? I know I can set the config from C CPI where
LOGARCHMETH1 got set. After setting this, transaction logs got archived but if I need to take a backup of the active transaction only?

Does transaction logs backup supports Full, Incremental, and Delta also?

db2 restart automatically on window

I have a package that uses the Db2 API for the archive of logs and also backup but when whenever i am uninstalling the package then db2 got stopped automatically and sometimes starts itself on the window. Is there any specific thing that I can look in db2diag for stopping and starting of db2 daemon?

MESSAGE : Obtained exclusive mode lock on the file:
H:\DB2\DB2TPS\DB2TPS\ctrl\db2stst.0000^M

FUNCTION: DB2 UDB, base sys utilities, DB2StopMain, probe:749^M
DATA #1 : String, 113 bytes^M
EntryId[001][Reason:LOCAL_INUSE] Active Db:[ ] [QsAttemptCnt=0] [DbActivated=1] [UseCnt=36] [DbQuiesced=0]^M

Add Warning about Python Versions for Models Stored in Tables

This issue is for the In DB Model Deployment and Management directory. I suggest that a note be added to the README discussing python version issues. I ran the steps in the readme and the UDF would fail with a "<class 'KeyError'>: 110" error. I created the ML model joblib and b64 string on my Mac that is running Python 3.7 and inserted the model into a table in my database. Then the UDF would fail with the indicated error. The database server is Linux running Python 3.9. I solved the problem by training the model and converting it to base64 on the database server. It should be noted that previous to this, I was getting the same error when my database was running Python 3.6. I upgraded the python version on the database server in an attempt to solve the problem.

where should I start

Hallo,
I will write one simple C or C++ prog with MS Visual Studio 2019,

connect to db2
execute a sql statement
write the result to console

where should I start?

Add REST API Example for ML Model using Db2 REST Endpoint

I am interested in adding Jupyter Notebooks to the "Deploying External Models with Python UDF" directory that show how to use the Db2 REST endpoint to create and execute a REST service to execute the linear regression that the other notebook in this directory creates. Using a REST service allows applications that need to call a ML model to do so without importing any Db2 libraries or even connecting directly to a database. I have developed three notebooks that show this and have attached them here in a zip file. They need to be cleaned up to before adding, but I wanted to see if you would accept this sort of example. Here are brief descriptions of the notebooks:

• Db2 RESTful External Py UDF.ipynb – Creates the REST Services and executes them.
• Db2 RESTful Prep.ipynb – Shows the grants and other database prep for creating and executing the procedures
• Db2 RESTful Endpoint Get Token Notebook.ipynb – Called by the Db2 RESTful External Py UDF.ipynb notebook to provide tokens needed to authenticate to the REST Endpoint.

Please let me know if this can be contributed.
Notebooks.zip

What do those files, "bldapp" "bldmc" "bldrtn", do?

What do those files, "bldapp" "bldmc" "bldrtn" (in the path db2-samples/cli/), do?
how can I rewrite the makefile just use gcc?

Documentation for the BAR sample scripts

Hello, i have docs to share.
They contain descriptions for the sample scripts on https://github.com/IBM/db2-samples/tree/master/BARVendor/ACS_scripts
They were once published on developerworks but got missing in action.

I'm ready to open a PR for this.

Thanks and Regards,
Martin

Contribution: db2mon for windows - powershell

Hi Team

first of all, Congratulation is opening db2-samples to the community. !!!

I was planning to convert db2mon.sh to powershell, so this useful tool could also be used on windows environments..

I am also an IBMer, and you can reach me at [email protected]

More detailed guides for developing C++ routine code for db2

I have been trying to develop and deploy some simple C++ routines and deploy them to db2. I used the udtfsampl.cpp file and followed these steps.

db2 prep udtfsampl.sqx bindfile

db2 bind udtfsampl.bnd

g++ -m64 -fPIC -c udtfsampl.C -std=c++14 -I/opt/ibm/db2/V11.5/include/ -D_REENTRANT

g++ -m64 -shared -o udtfsaml udtfsampl.o -L$DB2PATH -ldb2 -Wl,-rpath,$DB2PATH/$LIB -lpthread

cp udtfsampl $HOME/sqllib/function/

chmod 777 /database/config/db2inst1/sqllib/function/udtfsampl

db2stop force

db2start

CREATE OR REPLACE FUNCTION parsenames(VARCHAR(1024))
    RETURNS TABLE(product_id VARCHAR(200)) LANGUAGE  CPP
    PARAMETER STYLE  NPSGENERIC
    FENCED NOT THREADSAFE   NO FINAL CALL   DISALLOW PARALLEL   NO DBINFO
    NOT DETERMINISTIC   RETURNS NULL ON NULL INPUT   NO SQL
    EXTERNAL NAME '/database/config/db2inst1/sqllib/function/udtfsampl!parseNames';

db2 "select t.cust_id from orders t, table(parsenames(prod_codes)) as fi"

Result is an error code.

The documentation around this subject is sparse, confusing and incomplete - it would be great to have a 'Hello world` sample for something straightforward like a simple UDTF that returns some random numbers for example

Regression_Demo.ipynb Notebook - Create LINREG.GSTEST_STD table as Row Org to allow Alter Table

You will get an error in the "Standardize AGE in Test Data" cell if you are using Db2 Warehouse or other database that defaults to a column organized tables. The error happens in the step where the column is renamed in this cell. It appears that you can't rename columns in a column organized table. I fix this by changing this line:

sql = "CREATE TABLE LINREG.GSTEST_STD AS (SELECT * FROM LINREG.GSTEST) WITH DATA"

to this:

sql = "CREATE TABLE LINREG.GSTEST_STD AS (SELECT * FROM LINREG.GSTEST) WITH DATA ORGANIZE BY ROW"

file `repl/xmlpubtk/loadqueue/LoadQueue.jar` is corrupt

File /repl/xmlpubtk/loadqueue/LoadQueue.jar seems to be corrupt or non-standard in some way

Evidence:
download https://github.com/IBM/db2-samples/blob/master/repl/xmlpubtk/loadqueue/LoadQueue.jar
inspect with -

# jar tf /tmp/LoadQueue.jar
java.util.zip.ZipException: invalid END header (bad central directory offset)
	at java.util.zip.ZipFile.open(Native Method)
	at java.util.zip.ZipFile.<init>(ZipFile.java:247)
	at java.util.zip.ZipFile.<init>(ZipFile.java:172)
	at java.util.zip.ZipFile.<init>(ZipFile.java:143)
	at sun.tools.jar.Main.list(Main.java:1127)
	at sun.tools.jar.Main.run(Main.java:305)
	at sun.tools.jar.Main.main(Main.java:1300)

# file /tmp/LoadQueue.jar
/tmp/LoadQueue.jar: Java archive data (JAR)

Can't find COM.ibm.db2.app.*;

Hello,

I am trying to write my own Java UDFs. But it looks like the COM.ibm.db2.app dependency is missing.
Can you please provide pointers on where I can download it?

Sincerely,
Xinyu

Include Db2 vendor related header files to db2-samples repository

Hello,
For developing a vendor library solution for Db2 backup and restore, it seems that we need to include some header files from the Db2 install directory (e.g. /opt/IBM/db2/V11.5/include).

The compilation steps listed in the README https://github.com/IBM/db2-samples/tree/master/BARVendor require the header files from the Db2 install directory. As these header files are not part of this repository db2-samples, I wanted to understand the licensing model for the usage of these header files.
Could someone please assist in providing the licensing model for these header files? Is it fine to copy from the Db2 install directory and send a PR to this repository?

Thanks

db_library/views/db_table_quick_size.sql gives wrong index size if index tablespace not same pagesize as table tablespace

Need to update the code to join on the index tablespace to find the page size correctly for indexes