SQLCreatePkg

The SQLCreatePkg() function calls the bind utility, which prepares SQL statements that are stored in the bind file, and creates a package that is stored in the database.

Specification:

  • CLI 9.5
Unicode equivalent: The corresponding Unicode function is the SQLCreatePkgW() function. For information about ANSI to Unicode function mappings, see the Unicode functions (CLI) topic in the related concept section.

Syntax

SQLRETURN   SQLCreatePkg(
               SQLHDBC           hDbc, 
               SQLCHAR           *szBindFileNameIn, 
               SQLINTEGER        cbBindFileNameIn, 
               SQLCHAR           *szBindOpts, 
               SQLINTEGER        cbBindOpts)

Function arguments

Table 1. SQLCreatePkg() arguments
Data type Argument Use Description
SQLHDBC hDbc input Connection handle.
SQLCHAR* szBindFileNameIn input Name of a bind file to bind, or name of a file that contains a list of bind file names.
SQLINTEGER cbBindFileNameln input Number of SQLCHAR elements that are needed to store szBindFileNameIn, or SQL_NTS if szBindFileNameIn is null-terminated.
SQLCHAR* szBindOpts input List of bind options that are separated by semicolon.
SQLINTEGER cbBindOpts input Number of SQLCHAR elements that are needed to store szBindOpts, or SQL_NTS if szBindOpts is null-terminated.

Usage

The argument szBindFileNameIn is a string that contains the name of the bind file, or the name of a file that contains a list of bind file names. The bind file names must contain the extension .bnd. You can specify a path for these files. Precede the name of a bind list file with the at sign (@). The following example is a fully qualified bind list file name:
/u/user1/sqllib/bnd/@all.lst
The bind list file can contain one or more bind file names, and must have the extension .lst. Precede all but the first bind file name with a plus symbol (+). The bind file names can be on one or more lines. For example, the bind list file all.lst might contain the following lines:
mybind1.bnd+mybind2.bnd+
mybind3.bnd+
mybind4.bnd

You can use path specifications on bind file names in the list file. If no path is specified, the database manager takes path information from the bind list file.

When just the bind file name without any path is specified for the szBindFileNameIn parameter, the file will be first searched for in the current directory. If the file is found, it would be picked up and the package would be created. If the file is not found in the current directory, then the file would be automatically picked up from the instance or install path.

The following BIND command parameters can be specified with SQLCreatePkg():
  • BLOCKING={ALL | NO | UNAMBIG}
  • COLLECTION={schema name}
  • ENCODING={ASCII | EBCDIC | UNICODE | CCSID | integer} (Db2 for z/OS only)
  • GENERIC={OPTION1 VALUE1 OPTION2 VALUE2 ...}
  • GRANT={ PUBLIC | authid }
  • GRANT_ROLE={ role }
  • ISOLATION={CS | NC | RR | RS | UR}
  • KEEPDYNAMIC={YES | NO}
  • MSGFILE={MSG_FILE_NAME}
  • RELEASE={COMMIT | DEALLOCATE} (Db2 for z/OS only)
  • REOPT={NONE | ONCE | ALWAYS}

In Version 10.5 Fix Pack 2 and later, you can specify the RELEASE, GENERIC, and MSGFILE bind options with the SQLCreatePkg function. The MSGFILE bind option specifies the path and file name to which the bind operation outputs the messages. If you specify the file name only, without specifying the file path, output file is created in current working directory. When you are connecting to Db2 for z/OS Version 11 and later servers, you can specify the application compatibility special register setting (APPLCOMPAT) with the GENERIC bind option.

The BIND command parameters can be passed in as a string with name-value pairs separated by a semicolon. For example:
KEEPDYNAMIC=YES; ISOLATION=CS; BLOCKING=NO; RELEASE=COMMIT; GENERIC=APPLCOMPAT V11R1 IMMEDWRITE NO
Both options and values are not case-sensitive.

Example 1: Binding a list of files with REOPT=ONCE and ENCODING=CCSID.

strcpy (bindFileName, "insertEmp.bnd");
cliRC = SQLCreatePkg(hdbc,
                     bindFileName,
                     -3, // SQL_NTS
                     "REOPT=ONCE; ENCODING=CCSID",
                     -3, // SQL_NTS);

Example 2: Binding a list of files all with KEEPDYNAMIC=YES, BLOCKING=NO, and ISOLATION=RS.

strcpy (bindFileName, "/usr/ibm/dsdriver/bnd/@db2cli.lst");
cliRC = SQLCreatePkg(hdbc,
                     bindFileName,
                     strlen(bindFileName),
                     "KEEPDYNAMIC=YES; BLOCKING=NO; ISOLATION=RS",
                     -3, // SQL_NTS);

Example 3: Binding a file with COLLECTION=SCHEMA NAME.

strcpy (bindFileName, "insertEmp.bnd");
cliRC = SQLCreatePkg(hdbc,
                     bindFileName,
                     -3, // SQL_NTS
                     "REOPT=ONCE; ENCODING=CCSID; COLLECTION=NEWTON",
                     -3, // SQL_NTS);

Example 4: Binding a list of files with COLLECTION=XYZ, RELEASE=COMMIT, GENERIC option withAPPLCOMPAT=V11R1 and IMMEDWRITE=NO.

strcpy (bindFileName, "C:\Program Files\IBM\IBM DATA SERVER DRIVER\bnd\@db2cli.lst");
cliRC = SQLCreatePkg(hdbc,
                     bindFileName,
                     strlen(bindFileName),
                     "COLLECTION=XYZ;RELEASE=COMMIT;GENERIC= APPLCOMPAT V11R1  IMMEDWRITE NO",
                     -3, // SQL_NTS);

Example 5: Binding a list of files with MSGFILE=/TMP/bindOutput.txt, and REOPT=ONCE.

strcpy (bindFileName, "/usr/ibm/dsdriver/bnd/@db2cli.lst");
cliRC = SQLCreatePkg(hdbc,
                     bindFileName,
                     strlen(bindFileName),
                     "MSGFILE=/TMP/bindOutput.txt;REOPT=ONCE;", 
                     -3, // SQL_NTS);

Example 6: Binding a list of files and granting privileges to the user ZURBIE. The EXECUTE and BIND privileges are granted to the user ZURBIE.

strcpy (bindFileName, "/usr/ibm/dsdriver/bnd/@db2cli.lst");
cliRC = SQLCreatePkg(hdbc,
									bindFileName,
									strlen(bindFileName),
									"ACTION=REPLACE;GRANT='zurbie';MSGFILE=bindmsg.txt;
									RELEASE=DEALLOCATE;COLLECTION=XYZ;GENERIC=ENCODING 
									UNICODE keepdynamic yes;ISOLATION=SQL_TXN_READ_UNCOMMITTED;
									BLOCKING=NO;REOPT=NONE",
									-3, // SQL_NTS);