SQL Client Integration Exit Program


  Required Parameter Group:


  QSYSINC Member Name: ERWSCI

The SQL Client Integration exit program enables SQL applications to access data managed by a database management system other than the IBM® i relational database. An application requester driver (ARD) program is the generic term for this type of exit program. The two terms are used interchangeably throughout this topic. The system calls the ARD program during the following operations:

The CL commands that correspond to this exit program are the Add Relational Database Directory Entry (ADDRDBDIRE) and the Change Relational Database Directory Entry (CHGRBDDIRE) commands. Information about the ARD program must be defined to the system by adding it to the RDB directory using the ADDRDBDIRE command. Entries in the RDB directory that refer to ARD programs contain the keyword *ARDPGM in the remote location field. Each entry must identify the qualified ARD program name and the RDB name that it should be associated with. Also stored in the RDB directory entry is the level of interface that the ARD program expects to be called with. Currently the only value allowed is 1. An ARD program can be defined to process requests for several different RDBs by specifying the same ARD program for each RDB directory entry the ARD program is to process.


Restrictions

The following operations are not allowed in the ARD program or any program it calls:

DECLARE CURSOR statements must parse successfully on the application requester to be used through this interface.

The ARD program must be in a library that is part of the system auxiliary storage pool (ASP number 1) or a configured basic ASP (ASP numbers 2-32).

The following functions are not supported for the SQL Client Integration exit program interface:


Required Parameter Group

Interface level
INPUT; BINARY(4)

The level of the ARD program. The only value that will currently be passed is 1 because no value can be specified on the RDB directory commands. It is possible that updates to the interface could be made in the future.

For example, such updates could include:

  • Additional parameters.
  • Changes to the input format structures.
  • Changes to the SQLCA structure.
  • Changes to the SQLDA structure.
  • Changes to the output format structures.

Changes to enumerated values without changes to a structure will not result in a new interface level. Therefore, the ARD program should reject any unexpected values in the input format structures, or in the input SQLDA structure. In addition, the product identifier field on the ARCN0100 format can be used to determine the level of the local database that also identifies the values that could be expected for enumerated values in the input format and SQLDA structure. If updates are made to the interface, it may be possible for ARD programs to be registered with levels other than one by specifying the level on the RDB directory commands. At such time, a user registering the ARD program may incorrectly specify a level other than one for a program that only understands the level one interface. Therefore, ARD programs written to understand the current interface, the level one interface, should return an error if a level other than one is passed.

Input format
INPUT; CHAR(*);

The input format. The following table identifies the formats that the system will pass to the ARD program for each of the input format name values.

Note: General information about the nature of the functions associated with the various input formats listed may be found in the Distributed Relational Database Architecture™ Reference, SC26-4651. The chapter about the DRDA® processing model and command flows should be of particular interest in this regard.

Relationship between Input Format Name and Input Format


Length of input format
INPUT; BINARY(4)

The length of the input format in bytes.

Input format name
INPUT; CHAR(8)

The format of the information passed to the ARD program. The possible format names follow:

ARCN0100
Connect format. This format will be used by the system to pass information to the ARD program when a user or application attempts to connect to an RDB name corresponding to the ARD program. This format will always be passed to the ARD program before any other formats for a given connection. If running under commitment control, the system will register an RDB resource with commitment control. However, the provider of the ARD program must also register with commitment control using the commitment control APIs to be informed of commit and rollback requests so that it processes those requests and closes cursors as necessary. See Commit APIs for a description of how to use commitment control APIs with ARD programs. See Format ARCN0100 (Connect Format) for the structure of the input format that the system will pass to the ARD program for this input format name. See Output Connect Format for the structure of the output format that the ARD program must return to the system in response to this input format name.
ARDI0100
Disconnect format. This format will be used by the system to pass information to the ARD program when a user, application, or the system attempts to disconnect from an RDB name that corresponds to the ARD program. See Format ARDI0100 (Disconnect Format) for the structure of the input format that the system will pass to the ARD program for this input format name. No output format is returned from ARD program for this input format name.
ARBB0100
Begin package bind format. This format will be used by the system to pass information to the ARD program when a user or application attempts to create a package and specifies an RDB name corresponding to the ARD program. See Format ARBB0100 (Begin Package Bind Format) for the structure of the input format that the system will pass to the ARD program for this input format name. No output format is returned from the ARD program for this input format name.
ARBS0100
Bind statement for package creation format. This format will be used by the system to pass information about an SQL statement to the ARD program when a user or application attempts to create a package and specifies an RDB name corresponding to the ARD program. See Format ARBS0100 (Bind Statement for Package Creation Format) for the structure of the input format that the system will pass to the ARD program for this input format name. No output format is returned from ARD program for this input format name.
AREB0100
End of package bind format. This format will be used by the system to pass information to the ARD program about the end-the-package-creation function when a user or applications attempts to create a package and specifies an RDB name that corresponds to the ARD program. See Format AREB0100 (End of Package Bind Format) for the structure of the input format that the system will pass to the ARD program for this input format name. No output format is returned from ARD program for this input format name.
ARPS0100
Prepare statement format. This format will be used by the system to pass information about an SQL statement to the ARD program when an application attempts to prepare a statement. See Formats ARPS0100 and ARPD0100 (Prepare Format) for the structure of the input format that the system will pass to the ARD program for this input format name. No output format is returned from ARD program for this input format name.
ARPD0100
Prepare and describe format. This format will be used by the system to pass information about an SQL statement to the ARD program when an application attempts to prepare a statement and expects a description of the prepared statement to be returned into an SQL descriptor area (SQLDA). See Formats ARPS0100 and ARPD0100 (Prepare Format) for the structure of the input format that the system will pass to the ARD program for this input format name. See SQLDA for the structure of the output format that the ARD program must return to the system in response to this input format name.
ARXD0100
Execute bound statement that returns data format. This format will be used by the system to pass information about an SQL statement to the ARD program when an application attempts to execute a statement that expects data to be returned and was bound at package creation time by a call to the ARD program with format ARDSB001. An example of a statement that expects data to be returned is a SELECT INTO statement. See Formats ARXD0100 and ARXB0100 (Execute Bound Statement) for the structure of the input format that the system will pass to the ARD program for this input format name. See Output Execute Format for the structure of the output format that the ARD program must return to the system in response to this input format name.
ARXB0100
Execute bound statement that does not return data format. This format will be used by the system to pass information about an SQL statement to the ARD program when an application attempts to execute a statement that does not return data and was bound at package creation time by a call to the ARD program with format ARDSB001. See Formats ARXD0100 and ARXB0100 (Execute Bound Statement) for the structure of the input format that the system will pass to the ARD program for this input format name. The output format that the ARD program must return to the system in response to this input format name is a character (CHAR(1)) field containing an indication of whether the statement resulted in an update. An update is any operation that results in a change to an object such that the object is under commitment control.

Valid values follow:


ARXP0100
Execute prepared statement format. This format will be used by the system to pass information about an SQL statement to the ARD program when an application attempts to execute a statement that was previously prepared by a call to the ARD program with either format ARPS0100 or ARPD0100. See Format ARXP0100 (Execute Prepared Statement) for the structure of the input format that the system will pass to the ARD program for this input format name. The output format that the ARD program must return to the system in response to this input format name is a character (CHAR(1)) field containing an indication of whether the statement resulted in an update. An update is any operation that results in a change to an object such that the object is under commitment control.

Valid values follow:


ARXI0100
Execute immediate statement format. This format will be used by the system to pass information about an SQL statement to the ARD program when an application attempts to execute a statement that was not previously prepared. See Format ARXI0100 (Execute Immediate Statement Format) for the structure of the input format that the system will pass to the ARD program for this input format name. The output format that the ARD program must return to the system in response to this input format name is a character (CHAR(1)) field that contains an indication of whether the statement resulted in an update. An update is any operation that results in a change to an object such that the object is under commitment control.

Valid values follow:


AROC0100
Open a cursor format. This format will be used by the system to pass information about an SQL OPEN statement to the ARD program when an application attempts to execute the statement. See Format AROC0100 (Open Cursor Format) for the structure of the input format that the system will pass to the ARD program for this input format name. See Output Open Cursor Format for the structure of the output format that the ARD program must return to the system in response to this input format name.
ARFC0100
Fetch from a cursor format. This format will be used by the system to pass information about an SQL FETCH statement to the ARD program when an application attempts to execute the statement. See Format ARFC0100 (Fetch from a Cursor Format) for the structure of the input format the system will pass to the ARD program for this input format name. See Output Fetch Cursor Format for the structure of the output format that the ARD program must return to the system in response to this input format name.
ARCC0100
Close a cursor format. This format will be used by the system to pass information about an SQL CLOSE statement to the ARD program when an application attempts to execute the statement. See Format ARCC0100 (Close a Cursor Format) for the structure of the input format that the system will pass to the ARD program for this input format name. No output format is returned from ARD program for this input format name.
ARDS0100
Describe an SQL statement format. This format will be used by the system to pass information about an SQL DESCRIBE STATEMENT statement to the ARD program when an application attempts to execute the statement. See Format ARDS0100 (Describe a Statement Format) for the structure of the input format that the system will pass to the ARD program for this input format name. See SQLDA for the structure of the output format that the ARD program must return to the system in response to this input format name.
ARDT0100
Describe an SQL table format. This format will be used by the system to pass information about an SQL DESCRIBE TABLE statement to the ARD program when an application attempts to execute the statement. See Format ARDT0100 (Describe Object Format) for the structure of the input format that the system will pass to the ARD program for this input format name. See SQLDA for the structure of the output format that the ARD program must return to the system in response to this input format name.

SQLCA
OUTPUT; CHAR(136)

The SQL communication area. This is used for returning diagnostic information. The format of the structure is standard, and can be included using the INCLUDE SQLCA statement in an SQL program. The SQLCA has the following fields (shown in the C-language format):

struct sqlca
 {
    unsigned char  sqlcaid[8];
    long           sqlcabc;
    long           sqlcode;
    short          sqlerrml;
    unsigned char  sqlerrmc[70];
    unsigned char  sqlerrp[8];
    long           sqlerrd[6];
    unsigned char  sqlwarn[11];
    unsigned char  sqlstate[5];
 };

Fields that must be set by the ARD program prior to returning follow:



Another field in the SQLCA, sqlerrmc, is used to return additional pertinent information about the last statement run. Tokens in this field must be separated by X'FF' to be interpreted properly.

Each sqlcode has a corresponding message in message file QSQLMSGin library QSYS.For negative SQLCODEs and positive SQLCODEs other than +100, the corresponding message for the SQLCODE will be put in the job log. In addition, messages about how a statement ran are also put in the job log when running in debug mode. An ARD program can determine if the application is running in debug mode by using the debug APIs. The message ID is constructed by appending the absolute value (5 digits) of the sqlcode to SQ and changing the third character to L if the third character is a zero. For example, if the sqlcode is -501, the message identifier is SQL0501. Each message may optionally have replacement variables. These variables are placed in the sqlerrmc field of the SQLCA in the previous paragraph. A Display Message Description (DSPMSGD) command or format RTVM0300 of the Retrieve Message (QMHRTVM) API can be used to determine the correct length and type for replacement variables for a particular message. The sqlerrmc field for a message should be built up according to the field data for that message. See SQLCODEs and SQLSTATEs in the SQL programming topic collection for more information about SQLCODEs, SQLSTATEs, and their meaning.

CCSID
OUTPUT; BINARY(4)

The CCSID of the sqlerrm, sqlerrp, and sqlwarn fields in the SQLCA.

Output format
OUTPUT; CHAR(*)

The format of the information passed from the ARD program. The following table identifies the formats that the ARD program must return for each of the input format name values that the system will pass to it.

Relationship between Input Format Name and Output Format


Update performed. CHAR(1)

An indicator of whether the statement resulted in an update. An update is any operation that results in a change to an object such that the object is under commitment control. Valid values follow:


Length of output format
OUTPUT; BINARY(4)

The length of the output format in bytes. This must be zero for the following input format names: ARDI0100, ARBB0100, ARBS0100, AREB0100, ARPS0100, and ARCC0100.


Input Format Structures

In the following structures, the CCSID of the character fields is the job CCSID unless a specific CCSID field is included in the format for the field.


Format ARCN0100 (Connect Format)



Field Descriptions for Format ARCN0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

Device name. The device name that is specified in the directory entry. This will be blank if RMTLOCNAME(*ARDPGM) is specified for the RDB directory entry.

Local location name. The local location name that is specified in the directory entry. This will be blank if RMTLOCNAME(*ARDPGM) is specified for the RDB directory entry.

Mode name. The mode name that is specified in the directory entry. This will be blank if RMTLOCNAME(*ARDPGM) is specified for the RDB directory entry.

Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter for a description of the output format associated with this input format.

Password. The password that the application or user specified on the CONNECT statement. This field is blank if no password is specified. The system does not verify that this password is correct.

Product identifier. The product identifier for the local database in the form QSQvvrrm, where:

For example, if the local database is Version 3 Release 1 Modification 0 of DB2/400, the product identifier is QSQ03010.

RDB name. The name of the relational database that the request was directed to.

Remote location name. The remote location name that is specified in the RDB directory entry. This will be *ARDPGM if RMTLOCNAME(*ARDPGM) is specified for the RDB directory entry.

Remote network identifier. The remote network identifier that is specified in the directory entry. This will be blank if RMTLOCNAME(*ARDPGM) is specified for the RDB directory entry.

TPN name. The transaction program name that is specified in the directory entry. This will be blank if RMTLOCNAME(*ARDPGM) is specified for the RDB directory entry.

User ID. The user identifier that the application or user specified on the CONNECT statement. This field is blank if no user ID is specified.


Format ARDI0100 (Disconnect Format)



Field Descriptions for Format ARDI0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

Disconnect type. The system will set this field to indicate the type of disconnection that is being performed. Values passed follow:


Output format buffer size. The amount of storage allocated for the output format that is returned by theARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter for a description of the output format associated with this input format.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.


Format ARBB0100 (Begin Package Bind Format)



Field Descriptions for Format ARBB0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

Blocking type. An indicator of when blocking should be performed for read-only cursors in the program. This value may be overridden on the call to the ARD program with the AROC0100 format when the cursor is opened. Values passed follow:


CCSID. The CCSID of the text. This will always be set to 500.

Date format. The format that is used when the exit program accesses date result columns. Values passed follow (where m=month, d=day, and y=year):


Decimal delimiter. The statement decimal delimiter for the SQL statements. Values passed follow:


Default collection. The name of the collection identifier that is used for the unqualified names of the tables, views, indexes, and SQL packages. This parameter applies only to static SQL statements. A special value of *NONE indicates no default collection.

Errors allowed. Whether errors are allowed. Values passed follow:


Existence required. Whether existence of and authority to an object is required. Values passed follow:


Isolation level. The level of record locking that occurs under commitment control. Values passed follow:


Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Package collection. The collection for the package that is being created. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package that is being created. See Consistency Token for a description of this field.

Package name. The name of the package that is being created.

RDB name. The name of the relational database that the request was directed to.

Replace allowed. Whether the package can be replaced. Values passed follow:


Reserved. An ignored field.

String delimiter. The statement string delimiter for the SQL statements. Values passed follow:


Text. Text that briefly describes the packages function.

Time format. The format that is used when the exit program accesses time result columns. Values passed follow (where h=hour, m=minute, and s=second):



Format ARBS0100 (Bind Statement for Package Creation Format)


Field Descriptions for Format ARBS0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

CCSID. The CCSID of the statement text.

Length of SQLDA. The length of the SQLDA structure that describes the host variables that are used on the statement. If zero, no host variables were used on the statement.

Length of SQL statement. The length of the SQL statement as contained in the program.

Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Offset to SQLDA. The offset from the start of the input format structure to the SQLDA structure that describes the host variables that are used on the statement. If zero, no host variables were used on the statement.

Offset to SQL statement. The offset from the start of the input format structure to the SQL statement as contained in the program.

Package collection. The collection for the package being created. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package being created. See Consistency Token for a description of this field.

Package name. The name of the package being created.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.

Section number. The section number of the statement. See Section Number for a description of this field.

SQLDA. An SQLDA structure that describes the host variables that are used on the statement. The SQLDA structure is described in SQLDA. The SQLDATA and SQLIND pointers are set to NULL for package creation.

SQL statement. The SQL statement as contained in the program except that :H has been substituted for the host variable identifiers.


Format AREB0100 (End of Package Bind Format)



Field Descriptions for Format AREB0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

Maximum section number. The last section number in the package. This value may be greater than the last number passed on a call to the ARD program with format ARBS0100 when section numbers are reserved. See Section Number for more information about section numbers.

Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Package collection. The collection for the package being created. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package being created. See Consistency Token for a description of this field.

Package name. The name of the package being created.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.


Formats ARPS0100 and ARPD0100 (Prepare Format)



Field Descriptions for Formats ARPS0100 and ARPD0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

CCSID. The CCSID of the statement text and statement name.

Date format. The format that is used when the exit program accesses date result columns. Values passed follow (where m=month, d=day, and y=year):


Decimal delimiter. The statement decimal delimiter for the SQL statements. Values passed follow:


Isolation level. The level of record locking that occurs under commitment control. Values passed follow:


Length of SQL statement. The length of the statement string being prepared.

Offset to SQL statement. The offset from the start of the input format structure to the statement string being prepared.

Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Package collection. The collection for the package that the statement is associated with. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package that the statement is associated with. See Consistency Token for a description of this field.

Package name. The name of the package that the statement is associated with.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.

Section number. The section number that the statement is associated with. See Section Number for more information about section numbers.

SQL statement. The statement string being prepared.

Statement name. The SQL statement name that is specified on the PREPARE statement.

String delimiter. The statement string delimiter for the SQL statements. Values passed follow:


Time format. The format that is used when the exit program accesses time result columns. Values passed follow (where h=hour, m=minute, and s=second):



Formats ARXD0100 and ARXB0100 (Execute Bound Statement)



Field Descriptions for Formats ARXD0100 and ARXB0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

CCSID. The CCSID of statement text.

Date format. The format that is used when the exit program accesses date result columns. Values passed follow (where m=month, d=day, and y=year):


Decimal delimiter. The statement decimal delimiter for the SQL statements. Values passed follow:


DECLARE PROCEDURE statement. The DECLARE PROCEDURE statement as contained in the program that is associated with the statement when the statement is a CALL statement. 1403 class A

Input SQLDA. An SQLDA structure that describes the host variables that are used on the statement. The SQLDA structure is described in the SQLDA.

Isolation level. The level of record locking that occurs under commitment control. Values passed follow:


Length of DECLARE PROCEDURE. If the statement being executed is a CALL statement and if 1 was returned for the include bound statements field when the ARD program was called using the ARCN0100 format, this field is the length of the associated DECLARE PROCEDURE statement as contained in the program. If there is no associated DECLARE PROCEDURE statement, the statement is not a CALL statement or if 0 was returned for the include bound statements field, this field is set to zero.

Length of input SQLDA. The length of the SQLDA structure that describes the input host variables that are used on the statement. If zero, no input host variables were used on the statement.

Length of procedure name. If the statement being executed is a CALL statement, this field is the length of the procedure name. Otherwise, this field is set to zero.

Length of SQL statement. If the value 1 was returned for the include bound statements field when the ARD program was called using the ARCN0100 format, this field is the length of the statement as contained in the program. Otherwise, this field is set to zero.

Offset to DECLARE PROCEDURE. If the statement being executed is a CALL statement and if 1 was returned for the include bound statements field when the ARD program was called using the ARCN0100 format, this field is the offset of the associated DECLARE PROCEDURE statement as contained in the program. If there is no associated DECLARE PROCEDURE statement, the statement is not a CALL statement, or if 0 was returned for the include bound statements field, this field is set to zero.

Offset to input SQLDA. The offset from the start of the input format structure to the SQLDA structure that describes the input host variables that are used on the statement. If zero, no input host variables were used on the statement.

Offset to procedure name. If the statement being executed is a CALL statement, this field is the offset from the start of the input format structure to the procedure name as contained in the CALL statement. Otherwise, this field is set to zero.

Offset to SQL statement. If 1 was returned for the include bound statements field when the ARD program was called using the ARCN0100 format, this field is the offset from the start of the input format structure to the SQL statement as contained in the program. Otherwise, this field is set to zero.

Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Package collection. The collection for the package that the statement is associated with. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package that the statement is associated with. See Consistency Token for a description of this field.

Package name. The name of the package that the statement is associated with.

Procedure name. If the statement being executed is a CALL statement, this field contains the procedure name as specified in the CALL statement.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.

Section number. The section number the statement is associated with. See Section Number for more information about section numbers.

SQL statement. The statement as contained in the program.

String delimiter. The statement string delimiter for the SQL statements. Values passed follow:


Time format. The format that is used when the exit program accesses time result columns. Values passed follow (where h=hour, m=minute, and s=second):



Format ARXP0100 (Execute Prepared Statement)



Field Descriptions for Format ARXP0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

CCSID. The CCSID of statement name.

Date format. The format that is used when the exit program accesses date result columns. Values passed follow (where m=month, d=day, and y=year):


Decimal delimiter. The statement decimal delimiter for the SQL statements. Values passed follow:


Isolation level. The level of record locking that occurs under commitment control. Values passed follow:


Length of procedure name. If the statement being executed is a CALL statement, this field is the length of the procedure name. This field will always be set to 0; it is reserved for future use.

Length of SQLDA. The length of the SQLDA structure that describes the host variables that are used on the statement. If zero, no host variables were used on the statement.

Offset to procedure name. If the statement being executed is a CALL statement, this field is the offset from the start of the input format structure to the procedure name as contained in the CALL statement. This field will always be set to 0; it is reserved for future use.

Offset to SQLDA. The offset from the start of the input format structure to the SQLDA structure that describes the host variables that are used on the statement. If zero, no host variables were used on the statement.

Output format buffer size. The amount of storage allocated for the output format that is returned by theARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Package collection. The collection for the package that the statement is associated with. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package that the statement is associated with. See Consistency Token for a description of this field.

Package name. The name of the package that the statement is associated with.

Procedure name. If the statement being executed is a CALL statement, this field contains the procedure name as specified in the CALL statement. This field will not be passed; it is reserved for future use.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.

Section number. The section number that the statement is associated with. See Section Number for more information about section numbers.

SQLDA. An SQLDA structure that describes the host variables that are used on the statement. The SQLDA structure is described in the SQLDA.

Statement name. The SQL statement name that is specified on the EXECUTE statement.

String delimiter. The statement string delimiter for the SQL statements. Values passed follow:


Time format. The format that is used when the exit program accesses time result columns. Values passed follow (where h=hour, m=minute, and s=second):



Format ARXI0100 (Execute Immediate Statement Format)



Field Descriptions for Format ARXI0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

CCSID. The CCSID of the statement text.

Date format. The format that is used when the exit program accesses date result columns. Values passed follow (where m=month, d=day, and y=year):


Decimal delimiter. The statement decimal delimiter for the SQL statements. Values passed follow:


Isolation level. The level of record locking that occurs under commitment control. Values passed follow:


Length of SQL statement. The length of the SQL statement to execute.

Offset to SQL statement. The offset from the start of the input format structure to the SQL statement to execute.

Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Package collection. The collection for the package that the statement is associated with. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package that the statement is associated with. See Consistency Token for a description of this field.

Package name. The name of the package that the statement is associated with.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.

Section number. The section number that the statement is associated with. See Section Number for more information about section numbers.

SQL statement. The SQL statement to execute.

String delimiter. The statement string delimiter for the SQL statements. Values passed follow:


Time format. The format that is used when the exit program accesses time result columns. Values passed follow (where h=hour, m=minute, and s=second):



Format AROC0100 (Open Cursor Format)



Field Descriptions for Format AROC0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

Blocking allowed. Whether blocking should be performed for the cursor. Values passed follow:


CCSID. The CCSID of the statement text and cursor name.

Cursor name. The cursor name that is specified on the OPEN statement.

Date format. The format that is used when the exit program accesses date result columns. Values passed follow (where m=month, d=day, and y=year):


DECLARE CURSOR statement. The DECLARE CURSOR statement as contained in the program that is associated with the OPEN statement.

Decimal delimiter. The statement decimal delimiter for the SQL statements. Values passed follow:


Isolation level. The level of record locking that occurs under commitment control. Values passed follow:


Length of DECLARE CURSOR. If 1 was returned for the include bound statements field when the ARD program was called using the ARCN0100 format, this field is the length of the associated DECLARE CURSOR statement as contained in the program. Otherwise, this field is set to zero.

Length of SQLDA. The length of the SQLDA structure that describes the host variables that are used on the statement. If zero, no host variables were used on the statement.

Offset to DECLARE CURSOR. If 1 was returned for the include bound statements field when the ARD program was called using the ARCN0100 format, this field is the offset of the associated DECLARE CURSOR statement as contained in the program. Otherwise, this field is set to zero.

Offset to SQLDA. The offset from the start of the input format structure to the SQLDA structure that describes the host variables that are used on the statement. If zero, no host variables were used on the statement.

Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Package collection. The collection for the package that the statement is associated with. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package that the statement is associated with. See Consistency Token for a description of this field.

Package name. The name of the package that the statement is associated with.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.

Section number. The section number that the statement is associated with. See Section Number for more information about section numbers.

SQLDA. An SQLDA structure that describes the host variables that are used on the statement. The SQLDA structure is described in the SQLDA.

String delimiter. The statement string delimiter for the SQL statements. Values passed follow:


Time format. The format that is used when the exit program accesses time result columns. Values passed follow (where h=hour, m=minute, and s=second):



Format ARFC0100 (Fetch from a Cursor Format)



Field Descriptions for Format ARFC0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

CCSID. The CCSID of the cursor name.

Cursor name. The cursor name that is specified on the FETCH statement.

Date format. The format that is used when the exit program accesses date result columns. Values passed follow (where m=month, d=day, and y=year):


Decimal delimiter. The statement decimal delimiter for the SQL statements. Values passed follow:


Isolation level. The level of record locking that occurs under commitment control. Values passed follow:


Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Package collection. The collection for the package that the statement is associated with. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package that the statement is associated with. See Consistency Token for a description of this field.

Package name. The name of the package that the statement is associated with.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.

Section number. The section number that the statement is associated with. See Section Number for more information about section numbers.

String delimiter. The statement string delimiter for the SQL statements. Values passed follow:


Time format. The format that is used when the exit program accesses time result columns. Values passed follow (where h=hour, m=minute, and s=second):



Format ARCC0100 (Close a Cursor Format)



Field Descriptions for Format ARCC0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

CCSID. The CCSID of the cursor name.

Cursor name. The cursor name specified on the CLOSE statement.

Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Package collection. The collection for the package that the statement is associated with. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package that the statement is associated with. See Consistency Token for a description of this field.

Package name. The name of the package that the statement is associated with.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.

Section number. The section number that the statement is associated with. See Section Number for more information about section numbers.


Format ARDS0100 (Describe a Statement Format)



Field Descriptions for Format ARDS0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

CCSID. The CCSID of the statement name.

Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

Package collection. The collection for the package that the statement is associated with. A collection is a name that provides a logical grouping for SQL objects.

Package consistency token. The consistency token for the package that the statement is associated with. See Consistency Token for a description of this field.

Package name. The name of the package that the statement is associated with.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.

Section number. The section number that the statement is associated with. See Section Number for more information about section numbers.

Statement name. The statement name that is specified on the DESCRIBE statement.


Format ARDT0100 (Describe Object Format)



Field Descriptions for Format ARDT0100

Activation group number. The activation group number of the program that is performing the request. See Activation Group for a description of what an activation group is.

CCSID. The CCSID of the object name.

Length of object name. The length of the name of the SQL object to describe.

Object name. The name of the SQL object to be described. This may be either a qualified or unqualified table name. If it is qualified, it will be in the SQL naming format, that is, a collection name followed by a period and an SQL identifier.

Offset to object name. The offset from the start of the input format structure to the name of the SQL object to describe.

Output format buffer size. The amount of storage allocated for the output format that is returned by the ARD program. The length of the output format must be less than or equal to this value. It must also conform to the description of the output format associated with this input format. See the description of the output format parameter on for a description of the output format associated with this input format.

RDB name. The name of the relational database that the request was directed to.

Reserved. An ignored field.


Output Format Structures

In the following structures, the CCSID of the character fields is the job CCSID unless a specific CCSID field is included in the format for the field.


Output Connect Format



Field Descriptions for Output Connect Format

Include bound statements. Whether statements that were sent at package-creation time should be included in run-time formats ARXD0100, ARXB0100, and AROC0100. Valid values follow:


Protected conversation. Whether a protected conversation is used for the connection. If the connection uses a protected conversation, the system rejects attempts by the application to run the DISCONNECT SQL statement. Connections that use protected conversations are only ended during commit and rollback processing. See the description of the disconnect type field (page Disconnect type) for format ARDI0100 for more information. Valid values follow:


Server level. An identifier for the level of the database server that is accessed by the ARD program. This value must be a character representation of a hexadecimal value. That is, it may only consist of the characters 0-9 and A-F.

Server product. An identifier for the database server that is accessed by the ARD program. The system does no verification of the value of this field.

Server release. An identifier for the release of the database server that is accessed by the ARD program. This value must be a character representation of a hexadecimal value. That is, it may only consist of the characters 0-9 and A-F.

Server version. An identifier for the version of the database server that is accessed by the ARD program. This value must be a character representation of a hexadecimal value. That is, it may only consist of the characters 0-9 and A-F. The system does no verification of the value of this field.

User ID. The user ID that is used at the server.


Output Execute Format



Field Descriptions for Output Execute Format

Offset to result set. The offset from the start of the output format to the result set. If the SQLCA indicates an error occurred, this field must be set to 0. If no data is returned, this field should be set to 0.

Offset to SQLDA. An offset from the start of the output format to the SQLDA structure that describes the columns for the results of the statement. This field can only have a value of 0 or a multiple of 16. If the SQLCA indicates an error occurred, this field must be set to 0. If the SQLCA does not indicate an error, this field cannot be 0.

Reserved. An ignored field.

Result set. The result for the SQL statement. Columns are contiguous with null indicators (if appropriate) that precede the column data. See Query (Fetch) Data Format for more information. If the offset to result set field is 0, this field must not be included in the output format.

Note: This null indicator is not the same as the NULL in the C language.

SQLDA. An SQLDA structure that describes the columns for the results of the statement. The SQLDA structure is described in SQLDA. If the offset to SQLDA field is 0, this field must not be included in the output format.

Update performed. Whether the statement resulted in an update. An update is any operation that results in a change to an object such that the object is under commitment control. Valid values follow:



Output Open Cursor Format



Field Descriptions for Output Open Cursor Format

Block data. Whether the ARD program will block the data. Valid values follow:


Cursor held. Whether the cursor is held open after commits. Valid values follow:


Offset to SQLDA. The offset from the start of the output format to the SQLDA structure that describes the columns for the results of the statement. This field can only have a value of 0 or a multiple of 16. If the SQLCA indicates that an error occurred, this field must be set to 0. If the SQLCA does not indicate an error, this field cannot be 0.

Reserved. An ignored field.

SQLDA. An SQLDA structure that describes the columns for the results of the statement. The SQLDA structure is described in SQLDA. If the offset to SQLDA field is 0, this field must not be included in the output format.


Output Fetch Cursor Format



Field Descriptions for Output Fetch Cursor Format

Cursor closed. Whether the cursor is closed. Valid values follow:


Offset to result set. The offset from the start of the output format to the result set. If no data is returned, this field should be set to 0.

Result set. The result for the SQL statement. Columns are contiguous with null indicators (if appropriate) that precede the column data. See Query (Fetch) Data Format for more information. If the offset to result set field is 0, this field must not be included in the output format.

Note: This null indicator is not the same as the NULL in the C language.


Activation Group

An activation group provides the following:

Connections are scoped to the activation group. Therefore, the activation group mark and the RDB name together are used to uniquely identify the connection. It is not possible to have more than one connection active with the same RDB name in the same activation group at a point in time. However, it is possible to have multiple connections with different RDB names in the same activation group and to have multiple connections with the same RDB name in different activation groups.


Consistency Token

The system associates a consistency token with every program. If a program is compiled again, a new consistency token is created. When a user or application creates a package with the CRTSQLxxx commands or the CRTSQLPKG command and an RDB that is associated with an ARD program is specified on the command, the package consistency token from the program along with a package name and package collection is passed to the ARD program. In addition, at program run time, the ARD program will be passed the package name, package collection, and package consistency token that are currently associated with the program.

The ARD program can use this information passed to it during run time to verify that information passed to it during package creation is correct for the instance of the program being run. If a package does not exist for the given package consistency token, package name, and package collection, the exit program should return messages SQLCODE (-805) and SQLSTATE (51002).


Section Number

When a user or application creates a package with the CRTSQLxxx commands or the CRTSQLPKG command and an RDB that is associated with an ARD program is specified on the command, statements contained in the program are passed to the ARD program. A section number is associated with the statements. A section number is a signed binary number ranging from 1 to 32767. Section numbers may not necessarily be consecutive.

Related statements share the same section numbers. Therefore, a cursor declared for a statement and each statement that references the declared statement or cursor (FETCH, EXECUTE, OPEN, CLOSE, PREPARE) have the same section number. However, each uniquely declared statement or cursor has a different section number.

The system assigns a unique section number to the following statements and any other statements that it does not understand:

The following statements are not passed to the ARD program during the package creation process. Also, local statements that are understood by the precompiler but do not result in calls to the ARD program at run time are not passed.


Query (Fetch) Data Format

Query data is returned as a continuous collection of columns. Multiple rows of data may also be returned for format ARFC0100 when the ARD program returned 1 for the block data field on format AROC0100. When multiple rows of data are returned, the rows are also contiguous.

If a column is a null-capable or a derived field (for example COL1/COL2), the column data is preceded with a 1-byte null indicator. The length of data returned for variable-length data types should be based on the length in the length indicator. It should not be padded to the length of the field. Representation of all data types is assumed to be in the format used by IBM i.

The null indicator is a 1-byte signed binary integer. If the null indicator is negative (between X'80' and X'FF', inclusive), no column data should follow the indicator. For data conversion errors, -2 (X'FE') should be used for the null indicator.

If a data conversion error occurs and the column is non-null-capable, an error sqlcode should be returned in the SQLCA. When an error (negative SQLCODE) is indicated in the SQLCA and the ARD program is not returning multiple rows, no data should be returned. When multiple rows of data are being returned for a query and the ARD program returns an error in the SQLCA, the row that the error applies to should not be included in the block and the row previous to the row in error should be the last row returned.

When a warning (positive SQLCODE) is indicated in the SQLCA and the ARD program is not returning multiple rows, the row should be returned. When multiple rows of data are being returned for a query and an ARD program returns a warning in the SQLCA, the row that the warning applies to should be the last row in the block.

If the ARD program has not indicated that the cursor is closed in the cursor closed field of the output format for ARFC0100, the system will call the ARD program to get additional rows when the application requests a row after the row that the warning or error applies to. If the ARD program indicated the cursor was closed, the system will not call the ARD program for that cursor again until the application performs another SQL OPEN.

The following illustration shows an example of two rows of data being returned for a FETCH. The two rows each consist of a null-capable smallint COL1, a non-null-capable CHAR(3) COL2, a null-capable smallint COL3, and a non-null-capable VARCHAR(20) COL4.

Hex Representation             Description
00                    Row 1 - Null byte for COL1 - not null
0001                  Row 1 - COL1 (smallint) value = 1
D1E6E3                Row 1 - COL2 (CHAR(3)) value =  JWT
FF                    Row 1 - Null byte for COL3 - null
0007D1C5C6C6D9C5E8    Row 1 - COL4 Length = 7 value = JEFFREY
FF                    Row 2 - Null byte for COL1 - null
D1D4C2                Row 2 - COL2 (CHAR(3)) value =  JMB
00                    Row 2 - Null byte for COL3 - not null
0002                  Row 2 - COL3 (smallint) value = 2
0004D1D6C8D5          Row 1 - COL4 Length = 4 value = JOHN

In response to format ARXD0100 when data is returned for a CALL statement, a null indicator must precede each field regardless of whether the field is null-capable or not. For any parameters declared as input-only on the DECLARE PROCEDURE statement, the null indicator must be set to X'80'.

The following illustration shows an example of the data returned for a CALL where the first parameter was an input-only parameter and the second was an output smallint.

Hex Representation            Description
80                    Parm 1 - Null byte - input only
00                    Parm 2 - Null byte - not null
0001                  Parm 2 - Value = 1

SQLDA

An SQLDA is a set of variables that describe either host variables or column attributes. Included in this topic are the SQLDA structure, the relevant settings for those fields that the ARD program returns to the operating system, and the relevant fields that are passed to the ARD program.

For more information about SQLDA, see SQL Descriptor Area (SQLDA) in the DB2® for IBM i® SQL reference topic collection.

The SQLDA has the following fields (shown in C-language format):

struct sqlda
{
    unsigned char  sqldaid[8];
    long           sqldabc;
    short          sqln;
    short          sqld;
    struct sqlvar
    {
        short         sqltype;
        short         sqllen;
        unsigned char sqlres[12];
        unsigned char *sqldata;
        short         *sqlind;
        struct sqlname
        {
            short        length;
            unsigned char data[30];
        } sqlname;
    } sqlvar[1];
};

In response to the ARPD0100, ARDS0100, and ARDT0100 formats, the following fields must be set on the return from the ARD program:

And the second n entries contain the following:

In response to formats AROC0100 and ARXD0100, the following fields must be set on the return from the ARD program:


On input to the ARD program on formats ARBS0100, ARXD0100, ARXB0100, ARXP0100, and AROC0100, the ARD program must interpret the SQLDA because it describes host variables to be used with the statement. The relevant fields are:



Commit APIs

To process commit and rollback requests, providers of ARD programs must register a commitment control resource. See Commit APIs for more information about the commitment control APIs. The following section assumes an understanding of the commit APIs.

The Add Commitment Resource (QTNADDCR) API should be called to add a commitment resource to a commitment definition. After the resource is added, the exit program specified on the Add Commitment Resource API is called during commitment control operations for the commitment definition. When registering commitment resources for use with ARD programs, the commitment resource should be added after the first successful operation after the ARCN0100 format call. It is best not to perform this operation as part of the ARCN0100 format call since once a commitment control API resource is registered, the commitment definition is no longer at a logical unit-of-work boundary. A CONNECT operation does not normally change the logical unit-of-work boundary of a commitment definition. If a commitment resource is registered during the ARCN0100 format call, the create SQL package (CRTSQLPKG) function fails.

The Remove Commitment Resource (QTNRMVCR) API should be called to remove a resource from a commitment definition. This API should be called after processing the ARDI0100 format call. It cannot be called during the ARDI0100 format call if the ARDI0100 format indicates that the disconnection is occurring as part of a commit or rollback. A disconnect type of 3 indicates that the disconnect is occurring as part of a commit or rollback. In this situation, the commit resource should be removed either during the next SQL operation the ARD program processes or during the next commit or rollback operation. In the latter case, the commitment control exit program can have the resource removed by using the changes ended field in the return information format. Until this resource is removed, the Create SQL Package (CRTSQLPKG) command will fail.

Exit program introduced: V3R6

[ Back to top | Database and File APIs | APIs by category ]