Process Extended Dynamic SQL (QSQPRCED) API

The Process Extended Dynamic SQL (QSQPRCED) API provides functions to process extended dynamic SQL statements in an SQL package object.

Authorities and Locks

Creating an SQL package requires that you have *ADD and *READ authority to the library that will contain the package. Using an existing SQL package requires that you have *OBJOPR and *READ authority to the package. To use the PREPARE function of the API, you must have *OBJOPR and *ADD authority to the package. To use a sort sequence table, you must have *USE authority to the table and *EXECUTE authority to the library containing the table. To delete a specified package, you must have *OBJEXIST authority to the package and *EXECUTE authority to the library containing the package.

Required Parameter Group

SQL communications area

OUTPUT; CHAR(136)

This is used for returning diagnostic information. It includes the SQLCODE variable, indicating whether an error has occurred. If SQLCODE has a value of 0 after a call to this API, the function was successful.

You should have this space declared in the program that calls this API. This parameter is considered output because the API uses the space to pass back information. The format of the structure is standard and can be included using the INCLUDE SQLCA statement in an SQL program. It is described more completely in the SQL programming and DB2^® for IBM^® i SQL reference topic collections.

SQL descriptor area

INPUT; CHAR(*)

This is used for you to pass information about the variables being used on a specific SQL statement. The SQLDA is used for passing the address, data type, length, and coded character set identifier (CCSID)for variables on an OPEN, EXECUTE, FETCH, or DESCRIBE function.

The format of the structure is standard and can be included using the INCLUDE SQLDA statement in an SQL program. It is described more completely in the SQL programming and DB2 for i SQL reference topic collections.

Function template format

INPUT; CHAR(8)

The format of the function template being used. The possible values are:

SQLP0100	Basic template
SQLP0110	Extended SQLP0100 template
SQLP0200	Template for using scrollable cursors or multiple row statement
SQLP0210	Extended SQLP0200 template
SQLP0300	Template for options that may improve query performance
SQLP0310	Extended SQLP0300 template
SQLP0400	Template for specifying additional options that apply to package creation.
SQLP0410	Extended SQLP0400 template
SQLP0500	Template for specifying the retrieval of SQL diagnostic information.

For more information, see SQLP0100 Format, SQLP0110 Format, SQLP0200 Format, SQLP0210 Format, SQLP0300 Format, SQLP0310 Format, SQLP0400 Format, SQLP0410 Format or SQLP0500 Format.

Function template

INPUT; CHAR(*)

A structure that determines the function to perform, the requested statement to process, and the SQL package to be used. This also contains the text of the statement, which is required for the PREPARE function. For the format of this parameter, see SQLP0100 Format, SQLP0110 Format, SQLP0200 Format, SQLP0210 Format, SQLP0300 Format, SQLP0310 Format, SQLP0400 Format, SQLP0410 Format or SQLP0500 Format.

Error code

I/O; CHAR(*)

The structure in which to return error information. For the format of the structure, see Error code parameter.

Optional Parameter Group 1

SQL statement output

OUTPUT; CHAR(*)

You should have this space declared in the program that calls this API. This parameter is considered output because the API uses the space to pass back information.

For function D, this is used for receiving SQL diagnostic information from the SQL diagnostic area. The SQL diagnostic area contains information about an SQL statement (other then a GET DIAGNOSTIC statement) that was executed prior to invoking the API.

The format of the data received in this space depends on the type of diagnostic item(s) being retrieved. You can specify statement, condition, or connection information item types to be retrieved on SQLP0500 Format template. For detailed description of the format of the retrieved data specific for an information item type, see Diagnostic Information Data Format.

This parameter is required for function D.

For functions other than D, this is an optional parameter that returns a structure with output information about the SQL statement. The number of bytes available must be set on input to the number of bytes available for the structure.

Offset		Type	Field	Use
Dec	Hex
0	0	BINARY(4)	Number of bytes available	Indicates number of bytes available for structure
4	4	BINARY(4)	Length of structure	Indicates length of structure returned
8	8	BINARY(4)	Cursor index	Can be passed in subsequent QSQPRCED calls for this cursor using SQLP0310 Format or SQLP0410 Format to improve performance
12	C	BINARY(4)	Statement index	Can be passed in subsequent QSQPRCED calls for this statement using SQLP0310 Format or SQLP0410 Format to improve performance
16	10	BINARY(4)	Length of statement name	Length of statement name found using function A
20	14	BINARY(4)	Offset to statement name	Offset to statement name found using function A. The offset is from the beginning of the SQL statement output structure
*	*	CHAR(*)	Statement name

Length of SQL statement output

INPUT; BINARY(4)

The length of SQL statement output.

This parameter is required for function D and it must be specified for this function with the minimum value of 8. If specified for other functions, it must have a minimum value of 16.

SQLP0100 Format

The following shows the format of the function template parameter for the SQLP0100 format. For detailed descriptions of the fields in the table, see Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	CHAR(1)	Function
1	1	CHAR(10)	SQL package name
11	B	CHAR(10)	SQL package library name
21	15	CHAR(10)	Main program name
31	1F	CHAR(10)	Main program library name
41	29	CHAR(18)	Statement name
59	3B	CHAR(18)	Cursor name
77	4D	CHAR(1)	Open options
78	4E	CHAR(1)	Using clause for describe
79	4F	CHAR(1)	Commitment control
80	50	CHAR(3)	Date format
83	53	CHAR(1)	Date separator
84	54	CHAR(3)	Time format
87	57	CHAR(1)	Time separator
88	58	CHAR(3)	Naming option
91	5B	CHAR(1)	Decimal point
92	5C	BINARY(2)	Blocking factor
94	5E	BINARY(2)	Statement length
96	60	CHAR(*)	Statement text

SQLP0110 Format

The following shows the format of the function template parameter for the SQLP0110 format. For detailed descriptions of the fields in the table, see Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	CHAR(1)	Function
1	1	CHAR(10)	SQL package name
11	B	CHAR(10)	SQL package library name
21	15	CHAR(10)	Main program name
31	1F	CHAR(10)	Main program library name
41	29	CHAR(18)	Statement name
59	3B	CHAR(18)	Cursor name
77	4D	CHAR(1)	Open options
78	4E	CHAR(1)	Using clause for describe
79	4F	CHAR(1)	Commitment control
80	50	CHAR(3)	Date format
83	53	CHAR(1)	Date separator
84	54	CHAR(3)	Time format
87	57	CHAR(1)	Time separator
88	58	CHAR(3)	Naming option
91	5B	CHAR(1)	Decimal point
92	5C	BINARY(2)	Blocking factor
94	5E	BINARY(4)	Offset to statement text length and statement text
98	62	CHAR(1)	Hex literal option
99	63	CHAR(1)	Statement length type
100	64	CHAR(12)	Reserved
*	*	BINARY(2) or BINARY(4)	Statement length
*	*	CHAR(*)	Statement text

SQLP0200 Format

The following shows the format of the function template parameter for the SQLP0200 format. For detailed descriptions of the fields in the table, see Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	CHAR(1)	Function
1	1	CHAR(10)	SQL package name
11	B	CHAR(10)	SQL package library name
21	15	CHAR(10)	Main program name
31	1F	CHAR(10)	Main program library name
41	29	CHAR(18)	Statement name
59	3B	CHAR(18)	Cursor name
77	4D	CHAR(1)	Open options
78	4E	CHAR(1)	Using clause for describe
79	4F	CHAR(1)	Commitment control
80	50	CHAR(3)	Date format
83	53	CHAR(1)	Date separator
84	54	CHAR(3)	Time format
87	57	CHAR(1)	Time separator
88	58	CHAR(3)	Naming option
91	5B	CHAR(1)	Decimal point
92	5C	BINARY(2)	Blocking factor
94	5E	BINARY(2)	Scrollable option
96	60	BINARY(2)	Position option
98	62	BINARY(4)	Relative record
102	66	BINARY(4)	Number of rows for multiple row statement
106	6A	BINARY(2)	Statement length
108	6C	CHAR(*)	Statement text

SQLP0210 Format

The following shows the format of the function template parameter for the SQLP0210 format. For detailed descriptions of the fields in the table, see Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	CHAR(1)	Function
1	1	CHAR(10)	SQL package name
11	B	CHAR(10)	SQL package library name
21	15	CHAR(10)	Main program name
31	1F	CHAR(10)	Main program library name
41	29	CHAR(18)	Statement name
59	3B	CHAR(18)	Cursor name
77	4D	CHAR(1)	Open options
78	4E	CHAR(1)	Using clause for describe
79	4F	CHAR(1)	Commitment control
80	50	CHAR(3)	Date format
83	53	CHAR(1)	Date separator
84	54	CHAR(3)	Time format
87	57	CHAR(1)	Time separator
88	58	CHAR(3)	Naming option
91	5B	CHAR(1)	Decimal point
92	5C	BINARY(2)	Blocking factor
94	5E	BINARY(2)	Scrollable option
96	60	BINARY(2)	Position option
98	62	BINARY(4)	Relative record
102	66	BINARY(4)	Number of rows for multiple row statement
106	6A	BINARY(4)	Offset to statement text length and statement text
110	6E	CHAR(1)	Hex literal option
111	6F	CHAR(1)	Statement length type
112	70	CHAR(16)	Reserved
*	*	BINARY(2) or BINARY(4)	Statement length
*	*	CHAR(*)	Statement text

SQLP0300 Format

The following shows the format of the function template parameter for the SQLP0300 format. For detailed descriptions of the fields in the table, see Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	CHAR(1)	Function
1	1	CHAR(10)	SQL package name
11	B	CHAR(10)	SQL package library name
21	15	CHAR(10)	Main program name
31	1F	CHAR(10)	Main program library name
41	29	CHAR(18)	Statement name
59	3B	CHAR(18)	Cursor name
77	4D	CHAR(1)	Open options
78	4E	CHAR(1)	Using clause for describe
79	4F	CHAR(1)	Commitment control
80	50	CHAR(3)	Date format
83	53	CHAR(1)	Date separator
84	54	CHAR(3)	Time format
87	57	CHAR(1)	Time separator
88	58	CHAR(3)	Naming option
91	5B	CHAR(1)	Decimal point
92	5C	BINARY(2)	Blocking factor
94	5E	BINARY(2)	Scrollable option
96	60	BINARY(2)	Position option
98	62	BINARY(4)	Relative record
102	66	BINARY(4)	Number of rows for multiple row statement
106	6A	CHAR(1)	Direct map
107	6B	CHAR(1)	Reuse SQLDA
108	6C	CHAR(1)	Name check
109	6D	CHAR(1)	Use pointers
110	6E	CHAR(1)	WITH HOLD
111	6F	CHAR(18)	User-defined field
129	81	CHAR(10)	Close file name
139	8B	CHAR(10)	Close library name
149	95	CHAR(1)	Reopen
150	96	CHAR(1)	Use performance area
151	97	CHAR(1)	Reserved
152	98	BINARY(2)	Maximum Scale
153	99	CHAR(1)	Maximum Precision
155	9B	CHAR(1)	Minimum Divide Scale
156	9C	BINARY(4)	Statement text CCSID
160	A0	PTR(SYP)	SQL-package system pointer
176	B0	PTR(SYP)	Main-program system pointer
192	C0	BINARY(2)	Statement length
194	C2	CHAR(*)	Statement text

SQLP0310 Format

The following shows the format of the function template parameter for the SQLP0310 format. For detailed descriptions of the fields in the table, see Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	CHAR(1)	Function
1	1	CHAR(10)	SQL package name
11	B	CHAR(10)	SQL package library name
21	15	CHAR(10)	Main program name
31	1F	CHAR(10)	Main program library name
41	29	CHAR(18)	Statement name
59	3B	CHAR(18)	Cursor name
77	4D	CHAR(1)	Open options
78	4E	CHAR(1)	Using clause for describe
79	4F	CHAR(1)	Commitment control
80	50	CHAR(3)	Date format
83	53	CHAR(1)	Date separator
84	54	CHAR(3)	Time format
87	57	CHAR(1)	Time separator
88	58	CHAR(3)	Naming option
91	5B	CHAR(1)	Decimal point
92	5C	BINARY(2)	Blocking factor
94	5E	BINARY(2)	Scrollable option
96	60	BINARY(2)	Position option
98	62	BINARY(4)	Relative record
102	66	BINARY(4)	Number of rows for multiple row statement
106	6A	CHAR(1)	Direct map
107	6B	CHAR(1)	Reuse SQLDA
108	6C	CHAR(1)	Name check
109	6D	CHAR(1)	Use pointers
110	6E	CHAR(1)	WITH HOLD
111	6F	CHAR(18)	User-defined field
129	81	CHAR(10)	Close file name
139	8B	CHAR(10)	Close library name
149	95	CHAR(1)	Reopen
150	96	CHAR(1)	Use performance area
151	97	CHAR(1)	Reserved
152	98	BINARY(2)	Maximum Scale
153	99	CHAR(1)	Maximum Precision
155	9B	CHAR(1)	Minimum Divide Scale
156	9C	BINARY(4)	Statement text CCSID
160	A0	PTR(SYP)	SQL-package system pointer
176	B0	PTR(SYP)	Main-program system pointer
192	C0	BINARY(4)	Offset to statement text length and statement text
196	C4	CHAR(1)	Hex literal option
197	C5	CHAR(1)	Statement length type
198	C6	BINARY(2)	Extended User-defined field length
200	C8	BINARY(4)	Offset to Extended User-defined field
204	CC	BINARY(4)	Length of additional fields
208	CC	BINARY(4)	Connection handle
212	D4	CHAR(1)	Autocommit option
213	D5	CHAR(1)	Extended indicator option
214	D6	CHAR(2)	Reserved
216	D8	BINARY(4)	Cursor index
220	DC	BINARY(4)	Statement index
224	E0	BINARY(4)	Length of extended cursor name
228	E4	BINARY(4)	Offset to extended cursor name
232	E8	BINARY(4)	Length of extended statement name
236	EC	BINARY(4)	Offset to extended statement name
240	F0	CHAR(1)	Concurrent access resolution
241	F1	CHAR(15)	Reserved
256	100	BINARY(4)	Length of CURRENT CLIENT_USERID special register
260	104	BINARY(4)	Offset to CURRENT CLIENT_USERID special register
264	108	BINARY(4)	Length of CURRENT CLIENT_WRKSTNNAME special register
268	10C	BINARY(4)	Offset to CURRENT CLIENT_WRKSTNNAME special register
272	110	BINARY(4)	Length of CURRENT CLIENT_APPLNAME special register
276	114	BINARY(4)	Offset to CURRENT CLIENT_APPLNAME special register
280	118	BINARY(4)	Length of CURRENT CLIENT_PROGRAMID special register
284	11C	BINARY(4)	Offset to CURRENT CLIENT_PROGRAMID special register
288	120	BINARY(4)	Length of CURRENT CLIENT_ACCTNG special register
292	124	BINARY(4)	Offset to CURRENT CLIENT_ACCTNG special register
*	*	BINARY(2) or BINARY(4)	Statement length
*	*	CHAR(*)	Statement text
*	*	CHAR(*)	Extended user-defined field
*	*	CHAR(*)	Extended cursor name
*	*	CHAR(*)	Extended statement name
*	*	CHAR(*)	CURRENT CLIENT_USERID special register
*	*	CHAR(*)	CURRENT CLIENT_WRKSTNNAME special register
*	*	CHAR(*)	CURRENT CLIENT_APPLNAME special register
*	*	CHAR(*)	CURRENT CLIENT_PROGRAMID special register
*	*	CHAR(*)	CURRENT CLIENT_ACCTNG special register

SQLP0400 Format

The following shows the format of the function template parameter for the SQLP0400 format. For detailed descriptions of the fields in the table, see Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	CHAR(1)	Function
1	1	CHAR(10)	SQL package name
11	B	CHAR(10)	SQL package library name
21	15	CHAR(10)	Main program name
31	1F	CHAR(10)	Main program library name
41	29	CHAR(18)	Statement name
59	3B	CHAR(18)	Cursor name
77	4D	CHAR(1)	Open options
78	4E	CHAR(1)	Using clause for describe
79	4F	CHAR(1)	Commitment control
80	50	CHAR(3)	Date format
83	53	CHAR(1)	Date separator
84	54	CHAR(3)	Time format
87	57	CHAR(1)	Time separator
88	58	CHAR(3)	Naming option
91	5B	CHAR(1)	Decimal point
92	5C	BINARY(2)	Blocking factor
94	5E	BINARY(2)	Scrollable option
96	60	BINARY(2)	Position option
98	62	BINARY(4)	Relative record
102	66	BINARY(4)	Number of rows for multiple row statement
106	6A	CHAR(1)	Direct map
107	6B	CHAR(1)	Reuse SQLDA
108	6C	CHAR(1)	Name check
109	6D	CHAR(1)	Use pointers
110	6E	CHAR(1)	WITH HOLD
111	6F	CHAR(18)	User-defined field
129	81	CHAR(10)	Close file name
139	8B	CHAR(10)	Close library name
149	95	CHAR(1)	Reopen
150	96	CHAR(1)	Use performance area
151	97	CHAR(1)	Reserved
152	98	BINARY(2)	Maximum Scale
153	99	CHAR(1)	Maximum Precision
155	9B	CHAR(1)	Minimum Divide Scale
156	9C	BINARY(4)	Statement text CCSID
160	A0	PTR(SYP)	SQL-package system pointer
176	B0	PTR(SYP)	Main-program system pointer
192	C0	CHAR(10)	Sort sequence table name
202	CA	CHAR(10)	Sort sequence library name
212	D4	CHAR(10)	Language identifier
222	DE	CHAR(1)	Allow copy of data
223	DF	CHAR(1)	Allow blocking
224	E0	BINARY(2)	Statement length
226	E2	CHAR(*)	Statement text

SQLP0410 Format

The following shows the format of the function template parameter for the SQLP0410 format. For detailed descriptions of the fields in the table, see Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	CHAR(1)	Function
1	1	CHAR(10)	SQL package name
11	B	CHAR(10)	SQL package library name
21	15	CHAR(10)	Main program name
31	1F	CHAR(10)	Main program library name
41	29	CHAR(18)	Statement name
59	3B	CHAR(18)	Cursor name
77	4D	CHAR(1)	Open options
78	4E	CHAR(1)	Using clause for describe
79	4F	CHAR(1)	Commitment control
80	50	CHAR(3)	Date format
83	53	CHAR(1)	Date separator
84	54	CHAR(3)	Time format
87	57	CHAR(1)	Time separator
88	58	CHAR(3)	Naming option
91	5B	CHAR(1)	Decimal point
92	5C	BINARY(2)	Blocking factor
94	5E	BINARY(2)	Scrollable option
96	60	BINARY(2)	Position option
98	62	BINARY(4)	Relative record
102	66	BINARY(4)	Number of rows for multiple row statement
106	6A	CHAR(1)	Direct map
107	6B	CHAR(1)	Reuse SQLDA
108	6C	CHAR(1)	Name check
109	6D	CHAR(1)	Use pointers
110	6E	CHAR(1)	WITH HOLD
111	6F	CHAR(18)	User-defined field
129	81	CHAR(10)	Close file name
139	8B	CHAR(10)	Close library name
149	95	CHAR(1)	Reopen
150	96	CHAR(1)	Use performance area
151	97	CHAR(1)	Reserved
152	98	BINARY(2)	Maximum Scale
153	99	CHAR(1)	Maximum Precision
155	9B	CHAR(1)	Minimum Divide Scale
156	9C	BINARY(4)	Statement text CCSID
160	A0	PTR(SYP)	SQL-package system pointer
176	B0	PTR(SYP)	Main-program system pointer
192	C0	CHAR(10)	Sort sequence table name
202	CA	CHAR(10)	Sort sequence library name
212	D4	CHAR(10)	Language identifier
222	DE	CHAR(1)	Allow copy of data
223	DF	CHAR(1)	Allow blocking
224	E0	BINARY(4)	Offset to statement text length and statement text
228	E4	CHAR(1)	Hex literal option
229	E5	CHAR(1)	Statement length type
230	E6	BINARY(2)	Extended User-defined field length
232	E8	BINARY(4)	Offset to Extended User-defined field
236	EC	BINARY(4)	Length of additional fields
240	F0	BINARY(4)	Connection handle
244	F4	CHAR(1)	Autocommit option
245	F5	CHAR(1)	Extended indicator option
246	F6	CHAR(2)	Reserved
248	F8	BINARY(4)	Cursor index
252	FC	BINARY(4)	Statement index
256	100	BINARY(4)	Length of extended cursor name
260	104	BINARY(4)	Offset to extended cursor name
264	108	BINARY(4)	Length of extended statement name
268	10C	BINARY(4)	Offset to extended statement name
272	110	CHAR(1)	Concurrent access resolution
273	111	CHAR(15)	Reserved
288	120	BINARY(4)	Length of CURRENT CLIENT_USERID special register
292	124	BINARY(4)	Offset to CURRENT CLIENT_USERID special register
296	128	BINARY(4)	Length of CURRENT CLIENT_WRKSTNNAME special register
300	12C	BINARY(4)	Offset to CURRENT CLIENT_WRKSTNNAME special register
304	130	BINARY(4)	Length of CURRENT CLIENT_APPLNAME special register
308	134	BINARY(4)	Offset to CURRENT CLIENT_APPLNAME special register
312	138	BINARY(4)	Length of CURRENT CLIENT_PROGRAMID special register
316	13C	BINARY(4)	Offset to CURRENT CLIENT_PROGRAMID special register
320	140	BINARY(4)	Length of CURRENT CLIENT_ACCTNG special register
324	144	BINARY(4)	Offset to CURRENT CLIENT_ACCTNG special register
*	*	BINARY(2) or BINARY(4)	Statement length
*	*	CHAR(*)	Statement text
*	*	CHAR(*)	Extended user-defined field
*	*	CHAR(*)	Extended cursor name
*	*	CHAR(*)	Extended statement name
*	*	CHAR(*)	CURRENT CLIENT_USERID special register
*	*	CHAR(*)	CURRENT CLIENT_WRKSTNNAME special register
*	*	CHAR(*)	CURRENT CLIENT_APPLNAME special register
*	*	CHAR(*)	CURRENT CLIENT_PROGRAMID special register
*	*	CHAR(*)	CURRENT CLIENT_ACCTNG special register

SQLP0500 Format

The following shows the format of the function template parameter for the SQLP0500 format. For detailed descriptions of the fields in the table, see Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	CHAR(1)	Function
1	1	CHAR(3)	Date format
4	4	CHAR(1)	Date separator
5	5	CHAR(3)	Time format
8	8	CHAR(1)	Time separator
9	9	CHAR(3)	Naming option
12	C	CHAR(1)	Decimal point
13	D	CHAR(3)	Reserved
16	10	BINARY(4)	Diagnostic information CCSID
20	14	BINARY(4)	Condition number
24	18	CHAR(4)	Reserved
28	1C	BINARY(4)	Offset to statement, condition, or connection information item types list
32	20	BINARY(4)	Number of statement, condition, or connection information items
*	*	ARRAY(*) of BINARY(4)	Statement, condition, or connection information item types list

Field Descriptions

Allow copy of data. Whether a copy of the data can be used in a SELECT statement. The valid values follow:

A	A copy of the data is used only when necessary.
S	The system determines whether to use the data retrieved directly from the database or to use a copy of the data. The decision is based on which method provides the best performance. If commitment control level is C or S and the Allow Blocking field is not L, or if the commitment control level is A or R, then a copy of the data is used only when it is necessary to run the query.
N	A copy of the data is not allowed. If a temporary copy of the data is required to perform the query, an error message is returned.

The allow copy of data value is required for function 1. It is ignored for other functions.

Allow blocking. Whether the database manager can use record blocking, and the extent to which blocking can be used for read-only cursors. The valid values follow:

S	*READ: Records are blocked for read-only retrieval of data for cursors the following conditions are met: N is specified for the commitment control field, which indicates that commitment control is not used. The cursor is declared with a FOR FETCH ONLY clause or there are no dynamic statements that could run a positioned UPDATE or DELETE statement for the cursor.
	You can specify S to improve the overall performance of queries that meet the above conditions and retrieve a large number of records.
F	*NONE: Rows are not blocked for retrieval of data for cursors. If you specify F, the following occurs: Guarantees that the data retrieved is current. May reduce the amount of time required to retrieve the first row of data for a query. Stops the database manager from retrieving a block of data rows that is not used by the program when only the first few rows of a query are retrieved before the query is closed. Can degrade the overall performance of a query that retrieves a large number of rows.
L	*ALLREAD: Rows are blocked for read-only cursors if N or C is specified on the commitment control field. All cursors in a program that are not explicitly able to be updated are opened for read-only processing even though EXECUTE or EXECUTE IMMEDIATE statements may be in the program. If you specify L, the following occurs: Allows record blocking under commitment control level C in addition to the blocking allowed for S. Can improve the performance of almost all read-only cursors in programs, but limits queries in the following ways: A ROLLBACK statement or ROLLBACK HOLD SQL statement does not reposition a read-only cursor when L is specified. Dynamic running of a positioned UPDATE or DELETE statement (for example, using EXECUTE IMMEDIATE) cannot be used to update a row in a cursor unless the DECLARE statement for the cursor includes the FOR UPDATE clause.

The allow blocking value is required for function 1. It is ignored for other functions.

Autocommit Option. When using a connection handle with SQL Server Mode, the autocommit option can be used to direct the database to handle commitment control for the connection. The valid values follow:

'0'	Autocommit is not requested
'1'	Autocommit is requested.

When requested, the database will commit or rollback activity in the QSQSRVR server job after every SQL statement operation sent by the client, when that operation performs some committable work. No commit or rollback occurs after fetch operations. If the SQLCODE indicates a failure, the database will rollback the transaction. If the SQLCODE indicates success, the transaction will be committed.

When commitment control is set to *NONE, the database upgrades the commitment control level to *CHG, in the corresponding QSQSRVR job over the span of that operation.

When the connection handle is set to 0, this field must be set to '0'. This function is only valid for SQLP0310 Format and SQLP0410 Format.

Blocking factor. The number of records to be passed on a multiple row FETCH request. The same number should be used on the OPEN and the FETCH request. The blocking factor is required for functions 4 and 5. It is ignored for other functions.

Close file name. The name of the file for which all pseudo-closed open data paths should be closed. The file name must be the system file name. It cannot be an SQL long table name. If all pseudo-closed open data paths for the job are to be closed, the close file name and the close library name should be specified as *ALL. The close file name is required for function B. It is ignored for other functions.

If the close library name is *NUMBER or *THRESHOLD, then the first 4 bytes of close file name should contain an integer value. For *NUMBER, the value indicates the number of pseudo-closed cursors to close. For *THRESHOLD, the value indicates the threshold of pseudo-closed cursors that should remain following the closing of pseudo-closed cursors.

Close library name. The library of the close file name. If the close file name is specified as *ALL, the close library name should be *ALL as well. The close library name is required for function B. It is ignored for other functions.

*NUMBER indicates to close a specified number of pseudo-closed cursors. *THRESHOLD indicates to continue closing pseudo-closed cursors until a specified threshold is reached.

Commitment control. The commit level to be used. The possible values are:

C	*CHG
S	*CS
A	*ALL
N	*NONE

The commitment control value is required for function 1. It is ignored for other functions.

Concurrent access resolution. The concurrent access resolution for the statement.

The possible values are :

W	Wait For Outcome
S	Skip Locked Data
U	Use Currently Committed

This field is only valid for SQLP0310 Format and SQLP0410 Format.

Condition number. The number that identifies a condition for which diagnostic information items are to be retrieved from the SQL diagnostic area. See Key Values and Data Types of Condition Diagnostic Information Items for a list of the keys of the condition information item types that may be retrieved for the condition. See Key Values and Data Types of Connection Diagnostic Information Items for a list of the keys of the connection information item types that may be retrieved for the condition.

Connection Handle. The connection handle allows SQL Server Mode users to have a one-to-one correspondence with a specific QSQSRVR prestart job in the QSYSWRK subsystem.

When the negated connection handle is passed as input, serialization within the job is not held during the execution of the work handed off to the QSQSRVR job. Using the negated connection handle allows an application to achieve parallel execution across threads which are using different connection handles. If the connection handle were 14, it would be valid to input -14 if the user wanted parallel execution where other threads within this job would be allowed to execute when using a connection handle other than 14.

The Extended Dynamic Remote SQL (EDRS) APIs can be used to establish connections. Connect to EDRS Server (QxdaConnectEDRS) is used to initiate a connection to a server system. The connection handle returned by this API is valid only in the same job and activation group in which it was generated. A connection cannot span multiple jobs or activation groups. The Extended Dynamic Remote SQL (EDRS) APIs will utilize QSQSRVR jobs when SQL Server Mode is ON and a local connection is requested.

This input field is ignored when SQL Server Mode is not active. SQL Server Mode can be enabled within the job by calling the Change Job (QWTCHGJB) API. When connections are established, completion message SQL7908 is sent to the SQL Server Mode client joblog, indicating which QSQSRVR job is being used for that connection. The commitment definition is owned by the job that is indicated in this message.

This function is only valid for SQLP0310 Format and SQLP0410 Format.

CURRENT CLIENT_USERID special register. The CURRENT CLIENT_USERID special register contains the value of the client user ID from the client information specified for the current connection. This field is only valid for SQLP0310 Format and SQLP0410 Format.

CURRENT CLIENT_WRKSTNNAME special register. The CURRENT CLIENT_WRKSTNNAME special register contains the value of the workstation name from the client information specified for the current connection. This field is only valid for SQLP0310 Format and SQLP0410 Format.

CURRENT CLIENT_APPLNAME special register. The CURRENT CLIENT_APPLNAME special register contains the value of the application name from the client information specified for the current connection. This field is only valid for SQLP0310 Format and SQLP0410 Format.

CURRENT CLIENT_PROGRAMID special register. The CURRENT CLIENT_PROGRAMID special register contains the value of the client program ID from the client information specified for the current connection. This field is only valid for SQLP0310 Format and SQLP0410 Format.

CURRENT CLIENT_ACCTNG special register. The CURRENT CLIENT_ACCTNG special register contains the value of the accounting string from the client information specified for the current connection. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Cursor Index. An optional input performance value for functions 4, 5, 6, and 8. The value from the SQL Statement Output from a previous QSQPRCED call for this cursor should be passed. By passing the cursor index the request will be processed faster, since the database manager will not need to search for the matching cursor. All other fields relevant to the cursor still need to be set. This function is only valid for SQLP0310 Format and SQLP0410 Format.

Cursor name. The name of the SQL cursor. Cursor Name or Extended Cursor Name is required for functions 4, 5, 6, and 8. It is ignored for other functions. The cursor name is limited to 18 characters when specifying it here. When using SQLP0310 Format and SQLP0410 Format, a longer cursor name can be specified by using the Length of Extended Cursor Name and Offset to Extended Cursor Name. When the Extended Cursor Name is specified, this field is ignored.

Date format. The format used when accessing date result columns. All output date fields are returned in the format you specify. For input date strings, the value you specify is used to determine whether the date is a valid format. The valid values are:

USA	IBM USA standard (mm.dd.yyyy, hh:mm a.m., hh:mm p.m.)
ISO	International Standards Organization (yyyy-mm-dd, hh.mm.ss)
EUR	IBM European Standard (dd.mm.yyyy, hh.mm.ss)
JIS	Japanese Industrial standard Christian Era (yyyy-mm-dd, hh:mm:ss)
MDY	Month/day/year (mm/dd/yy)
DMY	Day/month/year (dd/mm/yy)
YMD	Year/month/day (yy/mm/dd)
JUL	Julian (yy/ddd)

The date format is required for function 1. For function D, it must be set to X'000000'. It is ignored for other functions.

Date separator. The separator used when accessing date result columns. The valid values are:

/	Slash separator
.	Period separator
,	Comma separator
-	Dash separator
blank	Blank separator

The date separator is required for function 1. For function D, it must be set to X'00'. It is ignored for other functions.

Decimal point. The decimal point for numeric constants in SQL statements. The valid values are:

.	Period separator
,	Comma separator

The decimal point is required for function 1. For function D, it must be set to X'00'. It is ignored for other functions.

Diagnostic information CCSID. CCSID of any CHAR data that is returned for the statement, condition, or connection diagnostic information. If 0 is specified, the data returned will be in the default job CCSID.

Direct map. Whether the data that is retrieved is to be moved directly into the user area. The possible values follow:

Y	Map the data to the user's area by using a single move operation. SQL obtains the address for the beginning of the user's area from the first SQLDATA entry of the SQLDA. The SQLDA must be set up correctly for all fields in the results list in case the direct map cannot be performed.
N	Use the SQLDA definitions to map the data to the user's area.

The direct map field is optional for function 5. The default value for direct map is N. It is ignored for all other functions.

Extended Cursor Name. The name of the SQL cursor. Cursor Name or Extended Cursor Name is required for functions 4, 5, 6, and 8. It is ignored for other functions.

Extended Indicator Option. Specifies whether statement prepared and executed from the package are enabled to use extended indicator support. The possible values are :

N	do not enable extended indicator support. This is the default.
Y	enable extended indicator support.

The extended indicator option is optional for function 1. It is ignored for other functions.

Extended Statement Name. The name of the SQL statement. Statement Name or Extended Statement Name is required for functions 2, 3, 4, 7, 9, and A. It is ignored for other functions.

Extended User-defined field. Up to 100 bytes of user-defined data that is inserted into the database performance monitor table. The data is only written to the table if you are collecting database performance monitor statistics by using the Start Database Monitor (STRDBMON) or the Start Performance Monitor (STRPFRMON) command. The user-defined field is optional for all functions. If this field is desired when you collect data, you should use it consistently for all functions.

Extended User-defined field length. The length of the extended user-defined field. The maximum length supported is 100. When the length contains zero, the user-defined field will be used instead of the extended user-defined field.

Function. The function being requested. The possible values follow:

1	Build a new package into the specified library.
2	Prepare a statement into the specified package.
3	Execute a statement from the specified package.
4	Open a cursor defined by a prepared statement in a package.
5	Fetch data from an open cursor.
6	Close an open cursor.
7	Describe a prepared statement in a package.
8	Close an open cursor and delete the open data path
9	Prepare and describe in one step.
A	Inquire as to whether or not a specified statement has been prepared in the specified package. The package can be searched for a statement name or statement text. To search on statement text, set the statement name to blanks on input. The statement name found is returned in the SQL statement output structure.
B	Actually close pseudo-closed cursors.
C	Delete the specified package.
D	Retrieve SQL diagnostic information. This function is only valid for SQLP0500 Format.

Hex literal option. Option which allows Hex literals to be treated as binary data instead of treating them as character data. The Hex literal option is used for function 1. It is ignored for all other functions. The valid values follow:

0	Treat Hex literals as character data. The default value is 0.
1	Treat Hex literals as binary data.

Language identifier. The language identifier to be used when *LANGIDUNQ or *LANGIDSHR is specified for the sort sequence table name. The valid values follow:

*JOB	The language identifier for the job is retrieved when the package is created.
*JOBRUN	The language identifier for the job is retrieved when the program is run.
language-id	The language identifier to be used by the program.

The language identifier value is required for function 1 when a sort sequence value of *LNGIDUNQ or *LNGIDSHR is specified. It is ignored for other functions.

Length of Additional Fields. When used, this field indicates how much space exists for additional fields in the input template. This field is being used to allow the template to be extended to include additional input fields. Set this length to indicate the additional space being passed.

For example, to pass the Connection Handle and Autocommit Option input fields, the length should be set to at least 5. When the length exceeds the size of any defined fields, the additional input storage must be set to hex zeros.

This function is only valid for SQLP0310 Format and SQLP0410 Format.

Length of CURRENT CLIENT_USERID special register. The length of the CLIENT_USERID special register. The maximum supported length is 255. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Length of CURRENT CLIENT_WRKSTNNAME special register. The length of the CLIENT_WRKSTNNAME special register. The maximum supported length is 255. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Length of CURRENT CLIENT_APPLNAME special register. The length of the CLIENT_APPLNAME special register. The maximum supported length is 255. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Length of CURRENT CLIENT_PROGRAMID special register. The length of the CLIENT_PROGRAMID special register. The maximum supported length is 255. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Length of CURRENT CLIENT_ACCTNG special register. The length of the CLIENT_ACCTNG special register. The maximum supported length is 255. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Length of Extended Cursor Name. The length of the Extended Cursor Name. The maximum supported length is 128. When specifying the Extended Cursor Name, Cursor Name is ignored. Set this field to 0 when using Cursor Name. Cursor Name or Extended Cursor Name must be passed for functions 4, 5, 6, and 8. It is ignored for other functions. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Length of Extended Statement Name. The length of the Extended Statement Name. The maximum supported length is 128. When specifying the Extended Statement Name, Statement Name is ignored. Set this field to 0 when using Statement Name. Statement Name or Extended Statement Name must be passed for functions 2, 3, 4, 7, 9, and A. It is ignored for other functions. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Main program library name. The library of the main program.

Main program name. The name of the program representing the top program in the SQL application. When this program completes, all cursors are closed and the SQL environment goes away. This program must be on the stack or an error will occur (SQL0901). The main program name is required for all functions except 1. This allows you to control the boundary of the application. If you want to scope to an activation group, as opposed to the main program name, this can be done by specifying *ENDACTGRP for the main program name. This special value is only allowed for function 1. For all other functions, specify the actual main program name.

Main-program system pointer. A system pointer that has been resolved to point to the main program. This field is ignored if the use pointers field has not been set to Y. If the use pointers field is specified, this field is used in place of the main program name and main program library name.

Maximum Precision. Specifies the maximum precision (length) that should be used for decimal operations. The possible values follow:

1	Maximum precision is 31.
2	Maximum precision is 63.

The Maximum Precision is optional for function 1. The default is 1. It is ignored for all other functions.

Maximum Scale. Specifies the maximum scale (number of decimal positions to the right of the decimal point) that should be used for decimal operations. The value can range from 0 to the Maximum Precision.

The Maximum Scale is optional for function 1. The default is 31. It is ignored for all other functions.

Minimum Divide Scale. Specifies the minimum divide scale (number of decimal positions to the right of the decimal point) that should be used for both intermediary and result data types. The value can range from '0' to '9' and may not exceed the Maximum Scale.

The Minimum Divide Scale is optional for function 1. The default is '0'. It is ignored for all other functions.

Name check. Whether the statement names and cursor names are to be completely checked for valid name syntax. The possible values follow:

Y	Check the names for valid name syntax.
N	Do not check the names for valid syntax.

The name check field is optional. The default value for name check is Y. It is ignored for functions 1 and B.

Naming option. The naming convention used for naming objects in SQL statements. The valid values are:

SYS	library/file syntax
SQL	collection/table syntax

The naming option is required for function 1. For function D, it must be set to X'000000'. It is ignored for other functions.

Number of rows for multiple row statement. When executing a multiple row INSERT or MERGE statement, this value indicates how many rows to process. The use of the SQLDA is similar to multiple row FETCH using SQLDA. See the DB2 for i SQL reference topic collection for instructions on how to set up the SQLDA to do multiple row FETCH. Refer to Multiple Row Statement Using SQLDA Setup Requirements for multiple row INSERT and MERGE requirements that are different from multiple row FETCH.

The prepared statement must be either a multiple row INSERT with a parameter marker specified for the number of rows, or a multiple row MERGE .

The number of rows is required for function 3 when the statement is a multiple row INSERT or MERGE. It is ignored for all other functions.

Number of statement, condition, or connection information items. The number of items specified in the statement, condition, or connection information item types list. If 0 is specified, no information items will be returned.

Offset to CURRENT CLIENT_USERID special register. Offset from beginning of SQLP00310 Format or SQLP00410 Format to the start of the CURRENT CLIENT_USERID special register field. Set this offset to 0 when the length of CURRENT CLIENT_USERID special register is zero. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Offset to CURRENT CLIENT_WRKSTNNAME special register. Offset from beginning of SQLP00310 Format or SQLP00410 Format to the start of the CURRENT CLIENT_WRKSTNNAME special register field. Set this offset to 0 when the length of CURRENT CLIENT_WRKSTNNAME special register is zero. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Offset to CURRENT CLIENT_APPLNAME special register. Offset from beginning of SQLP00310 Format or SQLP00410 Format to the start of the CURRENT CLIENT_APPLNAME special register field. Set this offset to 0 when the length of CURRENT CLIENT_APPLNAME special register is zero. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Offset to CURRENT CLIENT_PROGRAMID special register. Offset from beginning of SQLP00310 Format or SQLP00410 Format to the start of the CURRENT CLIENT_PROGRAMID special register field. Set this offset to 0 when the length of CURRENT CLIENT_PROGRAMID special register is zero. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Offset to CURRENT CLIENT_ACCTNG special register. Offset from beginning of SQLP00310 Format or SQLP00410 Format to the start of the CURRENT CLIENT_ACCTNG special register field. Set this offset to 0 when the length of CURRENT CLIENT_ACCTNG special register is zero. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Offset to Extended Cursor Name. Offset from beginning of SQLP00310 Format or SQLP00410 Format to the start of the cursor name field. When specifying the cursor name using this field and Length of Extended Cursor Name, Cursor Name is ignored. Set this field to 0 when using Cursor Name. Cursor Name or Extended Cursor Name must be passed for functions 4, 5, 6, and 8. It is ignored for other functions.This field is only valid for SQLP0310 Format and SQLP0410 Format.

Offset to Extended Statement Name. Offset from beginning of SQLP00310 Format or SQLP00410 Format to the start of the statement name field. When specifying the statement name using this field and Length of Extended Statement Name, Statement Name is ignored. Set this field to 0 when using Statement Name. Statement Name or Extended Statement Name must be passed for functions 2, 3, 4, 7, 9, and A. It is ignored for other functions. This field is only valid for SQLP0310 Format and SQLP0410 Format.

Offset to statement, condition, or connection information item type list. Offset from beginning of SQLP0500 Format to the list of statement, condition, or connection information item types.

Offset to statement text length and statement text. Offset from beginning of SQLP00110 Format, SQLP00210 Format, SQLP00310 Format, or SQLP00410 Format to the start of the statement text length field. The statement text should immediately follow the statement text length. This must be 0 for all functions other than 2, 3, 4, 7, 9, and A.

Offset to Extended User-defined field. Offset from beginning of SQLP00310 Format or SQLP00410 Format to the start of the extended user-defined field. When the offset is set to zero, the user-defined field will be used instead of the extended user-defined field.

Open options. The open options used on an SQL cursor. These are specified using the following bits:

Bit(0)	Read
Bit(1)	Write
Bit(2)	Update
Bit(3)	Delete

For example, if a cursor is only for FETCH statements, the bit pattern should be '10000000'B or hex 80. If update capability is needed, the bit pattern should be '10100000'B. The syntax in the SQL statement takes precedence over the open options. This means that the FOR UPDATE OF and FOR FETCH ONLY clauses will be honored, even if they do not coincide with the requested open options. The open options are required for functions 2 and 4. They are ignored for other functions.

Position option. The positioning option that is used for a FETCH statement. For options other than NEXT, the cursor must have been opened as a scrollable cursor. The valid options are:

0	FETCH NEXT
1	FETCH PRIOR
2	FETCH FIRST
3	FETCH LAST
5	FETCH BEFORE
6	FETCH AFTER
6	FETCH CURRENT
7	FETCH RELATIVE

The position option is required for function 5. It is ignored for other functions.

Relative record. The number of rows forward or backward to move before retrieving data. A positive number means forward and a negative number, backward. This is required when using function 5 (FETCH) with a position option of FETCH RELATIVE. It is ignored for other options.

Reopen. Whether to allow a cursor that is currently open to be reopened. A reopen operation implicitly closes and opens the cursor. If a reopen operation is requested on a cursor that is currently closed, only an open operation is performed (no implicit close takes place). The valid values follow:

0	Do not allow an open cursor to be reopened.
1	Allow an open cursor to be reopened.

The reopen field is optional for function 4 with a default of 0. It is ignored for all other functions.

Use performance area. Use a performance area internally to store information about the invocation environment. This option is beneficial in environments where statements are run repeatedly. The valid values follow:

0	Do not use the internal performance area. The default value is 0.
1	Use the internal performance area.

Reserved. All reserved fields must be set to X'00'.

Reuse SQLDA. Whether the SQLDA is being used again without changes. The possible values follow:

Y	SQLDA is being reused without changes. Do not validate the SQLDA.
N	SQLDA is not being reused. Validate the SQLDA.

The reuse SQLDA field is optional for functions 3, 4, and 5. The default value for reuse SQLDA is N. It is ignored for all other functions.

Scrollable option. Specified if the cursor is scrollable. The cursor must be opened as scrollable if any FETCH options other than FETCH NEXT are used. The valid values are:

0	Cursor is not scrollable
1	Cursor is scrollable

The scrollable option is required for function 4. It is ignored for other functions.

Sort sequence table name. The sort sequence table name to be used for string comparisons in SQL statements. The possible values follow:

*JOB	The sort sequence value for the job is retrieved when the package is created.
*JOBRUN	The sort sequence value for the job is retrieved when the program is run.
*LANGIDUNQ	The unique-weight sort table for the language that is specified on the language identifier field is used.
*LANGIDSHR	The shared-weight sort table for the language that is specified on the language identifier field is used.
*HEX	A sort sequence table is not used. The hexadecimal values of the characters are used to determine the sort sequence.
table-name	The name of the sort sequence table to be used.

The sort sequence table name value is required for function 1. It is ignored for other functions.

Sort sequence library name. The name of the sort sequence table can be qualified by one of the following library values:

*LIBL	All libraries in the job's library list are searched until the first match is found.
*CURLIB	The current library for the job is searched. If no library is specified as the current library for the job, the QGPL library is used.
library-name	The name of the library to be searched.

The sort sequence library name value is required for function 1 when a table name is specified for the sort sequence table name value. It is ignored for other functions.

SQL package library name. The library of the package.

SQL package name. The name of the SQL package used as the repository for the extended dynamic SQL statements. The SQL package must not be a distributed SQL package created through the Create SQL Package (CRTSQLPKG) or the Create SQL xxx (CRTSQLxxx) commands. Attempted use of a distributed SQL package results in SQL0827. The SQL package name is required for all functions. Function 1 checks the specified package name for valid name syntax. An invalid name results in SQL7023.

SQL-package system pointer. A system pointer that has been resolved to point to the SQL package. This option is ignored if the use pointers field has not been set to Y. If the use pointers field is specified, this field is used in place of the SQL package name and SQL package library name.

Statement, condition, or connection information item types list. The list of statement, condition, or connection information types that are to be retrieved from the SQL diagnostic area. A unique key identifies each item type. For the list of the keys that may be specified for item types, see Key Values and Data Types of Statement Diagnostic Information Items, Key Values and Data Types of Condition Diagnostic Information Items, or Key Values and Data Types of Connection Diagnostic Information Items.

Any one or all of the individual diagnostic information item types may be specified in the list. A default value will be returned if the diagnostic item is currently not set in the SQL diagnostic area.

Statement Index. An optional input performance value for functions 2, 3, 4, 7, 9, and A. The value from the SQL Statement Ouput from a previous QSQPRCED call for this statement should be passed. By passing the statement index the request will be processed faster, since the database manager will not need to search for the matching statement. All other fields relevant to the statement still need to be set. This function is only valid for SQLP0310 Format and SQLP0410 Format.

Statement length. The length of the SQL statement text that follows. The statement length is required for function 2, 9 and A. It is ignored for other functions.

Statement length type. Whether the SQL statement text is preceded by a two-byte or a four-byte statement length. The valid values are:

'0'	A two-byte statement length is used. This is the default.
'1'	A four-byte statement length is used.

Statement name. The name of the prepared SQL statement. Statement Name or Extended Statement Name is required for functions 2, 3, 4, 7, 9, and A. It is ignored for other functions. The statement name is limited to 18 characters when specifying it here. When using SQLP0310 Format and SQLP0410 Format, a longer statement name can be specified by using the Length of Extended Statement Name and Extended Statement Name Offset. When the Extended Statement Name is specified, this field is ignored.

Statement text. The SQL statement text that will be prepared. The statement text is required for function 2. It is ignored for other functions.

Statement text CCSID. The CCSID of the SQL statement text that will be prepared in this package. The statement text CCSID is optional for function 1. It is ignored for other functions. If the SQLP0100, SQLP0110, SQLP0200 or SQLP0210 formats are specified or if statement text CCSID is 0, the job CCSID is used.

Time format. The format used when accessing time result columns. All output time fields are returned in the format you specify. For input time strings, the value you specify is used to determine whether the time is a valid format. The valid values are:

HMS	Hour/minute/second (hh:mm:ss)
USA	IBM USA standard (mm.dd.yyyy, hh:mm a.m., hh:mm p.m.)
ISO	International Standards Organization (yyyy-mm-dd, hh.mm.ss)
EUR	IBM European Standard (dd.mm.yyyy, hh.mm.ss)
JIS	Japanese Industrial standard Christian Era (yyyy-mm-dd, hh:mm:ss)

The time format is required for function 1. For function D, it must be set to X'000000'. It is ignored for other functions.

Time separator. The separator used when accessing time result columns. The valid values are:

:	Colon separator
.	Period separator
,	Comma separator
blank	Blank separator

The time separator is required for function 1. For function D, it must be set to X'00'. It is ignored for other functions.

Use pointers. Whether the system pointers should be used to locate the main program and the SQL package instead of the symbolic names. The possible values follow:

0	Do not use pointers to the main program and the SQL package. The symbolic names are used to resolve to the objects.
1	Use the main-program and SQL-package system pointers instead of symbolic names. If 1 is specified, the pointers must address the main program and SQL package. The symbolic names are ignored. If 1 is specified, both pointers must be set.

The use pointers field is optional for all functions. The default value for the use pointers field is 0.

User-defined field. Up to 18 bytes of user-defined data that is inserted into the database performance monitor table. The data is only written to the table if you are collecting database performance monitor statistics by using the Start Database Monitor (STRDBMON) or the Start Performance Monitor (STRPFRMON) command. The user-defined field is optional for all functions. If this field is desired when you collect data, you should use it consistently for all functions.

Using clause for describe. The value to assign to each SQLNAME variable in the SQLDA. The possible values are:

N	Column names
L	Column labels
B	Both (SQLDA must be allocated for twice as many entries)
A	Any labels that exist

These are explained more completely in the DB2 for i SQL reference topic collection. The using clause is required for functions 7 and 9. It is ignored for other functions.

DLYPRP (delay PREPARE) is an option on an SQL precompile operation that cannot be specified on the creation of a package (function 1). DLYPRP(*NO) is used as the default.

See the DB2 for i documentation for a full description of all the options.

WITH HOLD. Whether the WITH HOLD SQL option should be applied to the statement. The possible values follow:

Y	The cursor is not closed as a consequence of a commit operation. The commit operation commits all the changes in the current unit of work but releases only locks that are not required to maintain the cursor.
N	The cursor is closed at the time of commit.

The WITH HOLD field is optional for functions 2 and 9. The default for WITH HOLD is N. It is ignored for all other functions.

Diagnostic Information Data Format

The following shows the format of the data returned in the SQL diagnostic information receiver when a statement, condition, or connection information item data is requested. For detailed descriptions of the fields in the table, see Diagnostic Information Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	BINARY(4)	Bytes returned
4	4	BINARY(4)	Bytes available
8	8	BINARY(4)	Number of diagnostic information items returned
12	C	ARRAY(*) of CHAR(*)	Diagnostic information items

The following shows the format of the diagnostic information item data returned in the SQL diagnostic information receiver for each diagnostic information item. For detailed descriptions of the fields in the table, see Diagnostic Information Field Descriptions.

Offset		Type	Field
Dec	Hex
0	0	BINARY(4)	Length of diagnostic information item
4	4	BINARY(4)	Key of diagnostic information item
8	8	Char(1)	Type of data returned
9	9	Char(3)	Reserved
12	C	BINARY(4)	Length of data returned
16	10	Char(*)	Data returned

Diagnostic Information Field Descriptions

Bytes available. The number of bytes of data available to be returned. All available data is returned if enough space is provided.

Bytes returned. The number of bytes of data returned.

Diagnostic information item. Diagnostic information item data returned in the SQL diagnostic information receiver for each diagnostic information item. For detailed description of the format of the returned data, see Diagnostic Information Data Format.

Data returned. Data returned for the diagnostic item identified by the key. See the DB2 for i SQL reference topic collection for information about the possible data value for the diagnostic item.

Key of diagnostic information item. Key that identifies the returned diagnostic information item. For a list of key values for each diagnostic information item available for retrieval, see Key Values and Data Types of Statement Diagnostic Information Items, Key Values and Data Types of Condition Diagnostic Information Items, or Key Values and Data Types of Connection Diagnostic Information Items.

Length of data returned. Length of data returned for the item.

Length of diagnostic information item. Length of the information returned for the item. This length can be used to access the next diagnostic information item.

Number of diagnostic information items returned. Number of diagnostic information returned in the space provided.

Reserved. A field that can be ignored.

Type of data returned. Type of the data returned for the item. The possible values are:

B	Binary data
C	Character data
D	Decimal data

Key Values and Data Types of Statement Diagnostic Information Items

Key	Type	SQL Get Diagnostics Item
101	CHAR(*)	COMMAND_FUNCTION
102	BINARY(4)	COMMAND_FUNCTION_CODE
103	BINARY(4)	DB2_RELATIVE_COST_ESTIMATE
104	BINARY(4)	DB2_DIAGNOSTIC_CONVERSION_ERROR
105	CHAR(*)	DB2_GET_DIAGNOSTICS_DIAGNOSTICS
106	BINARY(4)	DB2_LAST_ROW
107	BINARY(4)	DB2_NUMBER_CONNECTIONS
108	BINARY(4)	DB2_NUMBER_PARAMETER_MARKERS
109	BINARY(4)	DB2_NUMBER_RESULT_SETS
110	DECIMAL(31,0)	DB2_NUMBER_ROWS
111	BINARY(4)	DB2_NUMBER_SUCCESSFUL_SUBSTMTS
112	BINARY(4)	DB2_RETURN_STATUS
113	DECIMAL(31,0)	DB2_ROW_COUNT_SECONDARY
114	BINARY(4)	DB2_ROW_LENGTH
115	CHAR(1)	DB2_SQL_ATTR_CONCURRENCY
116	CHAR(1)	DB2_SQL_ATTR_CURSOR_CAPABILITY
117	CHAR(1)	DB2_SQL_ATTR_CURSOR_HOLD
118	CHAR(1)	DB2_SQL_ATTR_CURSOR_ROWSET
119	CHAR(1)	DB2_SQL_ATTR_CURSOR_SCROLLABLE
120	CHAR(1)	DB2_SQL_ATTR_CURSOR_SENSITIVITY
121	CHAR(1)	DB2_SQL_ATTR_CURSOR_TYPE
122	CHAR(*)	DYNAMIC_FUNCTION
123	BINARY(4)	DYNAMIC_FUNCTION_CODE
124	CHAR(1)	MORE
125	BINARY(4)	NUMBER
126	DECIMAL(31,0)	ROW_COUNT
127	BINARY(4)	TRANSACTION_ACTIVE
128	BINARY(4)	TRANSACTIONS_COMMITTED
129	BINARY(4)	TRANSACTIONS_ROLLED_BACK

Key Values and Data Types of Condition Diagnostic Information Items

Key	Type	SQL Get Diagnostics Item
201	CHAR(*)	CATALOG_NAME
202	CHAR(*)	CLASS_ORIGIN
203	CHAR(*)	COLUMN_NAME
204	CHAR(*)	CONDITION_IDENTIFIER
205	BINARY(4)	CONDITION_NUMBER
206	CHAR(*)	CONSTRAINT_CATALOG
207	CHAR(*)	CONSTRAINT_NAME
208	CHAR(*)	CONSTRAINT_SCHEMA
209	CHAR(*)	CURSOR_NAME
210	BINARY(4)	DB2_ERROR_CODE1
211	BINARY(4)	DB2_ERROR_CODE2
212	BINARY(4)	DB2_ERROR_CODE3
213	BINARY(4)	DB2_ERROR_CODE4
214	BINARY(4)	DB2_INTERNAL_ERROR_POINTER
215	BINARY(4)	DB2_LINE_NUMBER
216	CHAR(10)	DB2_MESSAGE_ID
217	CHAR(*)	DB2_MESSAGE_ID1
218	CHAR(*)	DB2_MESSAGE_ID2
219	BINARY(4)	DB2_MESSAGE_KEY
220	CHAR(*)	DB2_MODULE_DETECTING_ERROR
221	BINARY(4)	DB2_NUMBER_FAILING_STATEMENTS
222	BINARY(4)	DB2_OFFSET
223	CHAR(*)	DB2_ORDINAL_TOKEN_1
224	CHAR(*)	DB2_ORDINAL_TOKEN_2
225	CHAR(*)	DB2_ORDINAL_TOKEN_3
226	CHAR(*)	DB2_ORDINAL_TOKEN_4
227	CHAR(*)	DB2_ORDINAL_TOKEN_5
228	CHAR(*)	DB2_ORDINAL_TOKEN_6
229	CHAR(*)	DB2_ORDINAL_TOKEN_7
230	CHAR(*)	DB2_ORDINAL_TOKEN_8
231	CHAR(*)	DB2_ORDINAL_TOKEN_9
232	CHAR(*)	DB2_ORDINAL_TOKEN_10
233	CHAR(*)	DB2_ORDINAL_TOKEN_11
234	CHAR(*)	DB2_ORDINAL_TOKEN_12
235	CHAR(*)	DB2_ORDINAL_TOKEN_13
236	BINARY(4)	DB2_PARTITION_NUMBER
237	BINARY(4)	DB2_REASON_CODE
238	BINARY(4)	DB2_RETURNED_SQLCODE
239	BINARY(4)	DB2_ROW_NUMBER
240	CHAR(1)	DB2_SQLERRD_SET
241	BINARY(4)	DB2_SQLERRD1
242	BINARY(4)	DB2_SQLERRD2
243	BINARY(4)	DB2_SQLERRD3
244	BINARY(4)	DB2_SQLERRD4
245	BINARY(4)	DB2_SQLERRD5
246	BINARY(4)	DB2_SQLERRD6
247	BINARY(4)	DB2_TOKEN_COUNT
248	CHAR(70)	DB2_TOKEN_STRING
249	BINARY(4)	MESSAGE_LENGTH
250	BINARY(4)	MESSAGE_OCTET_LENGTH
251	CHAR(*)	MESSAGE_TEXT
252	CHAR(*)	PARAMETER_MODE
253	CHAR(*)	PARAMETER_NAME
254	BINARY(4)	PARAMETER_ORDINAL_POSITION
255	CHAR(5)	RETURNED_SQLSTATE
256	CHAR(*)	ROUTINE_CATALOG
257	CHAR(*)	ROUTINE_NAME
258	CHAR(*)	ROUTINE_SCHEMA
259	CHAR(*)	SCHEMA_NAME
260	CHAR(*)	SERVER_NAME
261	CHAR(*)	SPECIFIC_NAME
262	CHAR(*)	SUBCLASS_ORIGIN
263	CHAR(*)	TABLE_NAME
264	CHAR(*)	TRIGGER_CATALOG
265	CHAR(*)	TRIGGER_NAME
266	CHAR(*)	TRIGGER_SCHEMA

Key Values and Data Types of Connection Diagnostic Information Items

Key	Type	SQL Get Diagnostics Item
301	CHAR(*)	GET DIAGNOSTICS CONNECTION_NAME
302	CHAR(1)	DB2_AUTHENTICATION_TYPE
303	CHAR(*)	DB2_AUTHORIZATION_ID
304	CHAR(1)	DB2_CONNECTION_METHOD
305	BINARY(4)	DB2_CONNECTION_NUMBER
306	BINARY(4)	DB2_CONNECTION_STATE
307	BINARY(4)	DB2_CONNECTION_STATUS
308	BINARY(2)	DB2_CONNECTION_TYPE
309	BINARY(4)	DB2_DYN_QUERY_MGMT
310	CHAR(1)	DB2_ENCRYPTION_TYPE
311	CHAR(8)	DB2_PRODUCT_ID
312	CHAR(*)	DB2_SERVER_CLASS_NAME
313	CHAR(*)	DB2_SERVER_NAME

Multiple Row Statement Using SQLDA Setup Requirements

Just as in the case of a multiple row FETCH, the support for multiple row INSERT and MERGE with an SQLDA expects the users to have two contiguous areas. One is for the data and the other is for the indicators. The former contains rows of data (the number of rows is given on function 3 calls), and the latter contains rows of indicators.

If none of the columns is null capable, there is no need to have an indicator area. If any of the columns is null capable, all the columns should be turned into null capable (that is, sqltype in all the sqlvar entries should be an odd number), and the row indicator area should have as many indicators per row as there are columns.

In the SQLDA, the pointer sqldata in all the sqlvar entries should be pointing at the data elements for the first row. Similarly, the pointer sqlind in all the sqlvar entries should be pointing at the indicators for the first row, except in the case where there are no null-capable columns at all.

Usage Notes

This function is not threadsafe when called in the following way:

• Using a Data Definition Language (DDL) SQL statement, for example: CREATE, DROP or ALTER.

Error Messages

Message ID	Error Message Text
CPF24B4 E	Severe error while addressing parameter list.
CPF3C21 E	Format name &1 is not valid.
CPF3C90 E	Literal value cannot be changed.
CPF9872 E	Program or service program &1 in library &2 ended. Reason code &3.
SQL0204 E	&1. and &2 type &3 not found.
SQL0516 E	Prepare statement &2 not found.
SQL0901 E	SQL system error.
SQL7023 E	Parameter value not valid.

---

API introduced: V2R3

---

Top | Database and File APIs | APIs by category