IN data set
The //IN data set contains SQL statements, optional input commands, and comments and uses the format RECFM=FB,LRECL=80. The data in the columns 73-80 is ignored.
SQL statements
SQL statements may span across multiple records, and must be terminated with the SQL termination character or EOF. If a statement spans across multiple records, a space is inserted after each record.
Below is an example.
//IN DD *
SELECT FILEA_NUMB, FILEA_NAME, FILEA_ADDR, FILEA_PHONE,
FILEA_DATE, FILEA_AMT, FILEA_COMMENT FROM DALE1VSAM;
/*
Input commands
You can use ++SET statements to control some of driver operations. Each statement is in the following format.
++SET key=value;Every statement must begin in column 1 and must be contained in a single record. The ending SQL termination character is optional. Many ++SET commands affect the operation of the SQL statements that follow them. If a session is open and a ++SET command changes a value that can be set only by an OPEN call, an implicit CLOSE and OPEN operation is done to enable the value.
The following are the available commands.
| Commands | Valid values | Description |
|---|---|---|
| BUFSIZE= | 1-8 digits. The default value is 256. | Sets the size (in megabytes) of the 64-bit memory objects used to buffer the result set. This option may cause implicit CLOS and OPEN operation. |
| CCSID= | 1-5 digits. The default value is 0. | Sets the CCSID (Coded character set identifier) for the session. This is the CSID of the SQL statement. This parameter may cause implicit CLOS and OPEN operation. Also see ++SET RSCSID |
| DSID= | 1-4 characters. Data server ID | N/A | NONE. The default value is NONE. | Sets or removes the data server id (DB2 subsystem ID, LUW server name, or remote Server name). Setting DSID=N/A or DSID=NONE reverts the SQL92 Engine after setting a data server id. May cause implicit CLOS and OPEN operation. |
| DB2S= | Alias for ++SET DSID | |
| DVCPERF= | 1 character. Y | N. The default value is N. | Indicates whether DS Client performance statistics should be written to the RPT DD. |
| INTERIM= | 1-3 digits. nnn. The default value is None. | Requests interim row counts of (approximately) nnn seconds. |
| MODE= | MOVE | LOCATE. The default value is LOCATE | Indicates whether move mode or locate mode is used. May cause implicit CLOS and OPEN operation. |
| MRC= | Specifies the number of threads in the Map Reduce client. If MRC is specified without MRID, all MRC connections for the request is managed by AVZXMAPD. If MRID is specified, a single MRC connection is made. | |
| MRID= | Specifies the Map Reduce Client id number of the request. Specify a number less than or equal to MRNO to retrieve only a part of the result set. | |
| MRNO= | Alias for ++SET MRC | |
| NONULLIND= | Y | N. 1 character. | Indicates whether to suppress the NULL
indicator.
|
| NOVARLEN= | Y | N. 1 character. | Indicates whether to suppress VARCHAR length
field.
|
| NUMROWS= | 1-8 digits. The default value is 128. | Sets the number of rows returned with each RECV call. To request the maximum number of rows that fits in each segment, specify -1. May cause implicit CLOS and OPEN operation. |
| PASSWORD= | 1-8 characters. | Sets the password to be used for source data access. This parameter is used with ++SET USERID. If the statement is not coded, access is given with the current credentials for the address space. |
| RSCSID= | 1-5 digits. The default value is 0. | Sets the CCSID (Coded character set identifier) for the session. This is the CCSID of the result set. May cause implicit CLOS and OPEN operation. |
| SQCSID= | The default value is 0. | Alias for ++SET CCSID. Specifies the CCSID of the SQL statement. May cause implicit CLOS and OPEN operation. Also see ++SET RSCSID. |
| SQLERRRC= | 1-4 digits. The default value is 16. | Sets the return code to be used to indicate a SQL error. |
| SQLPERF= | Y | N. 1 character. The default value is N. | Indicates whether SQL performance statistics should be written to the RPT DD. |
| TIMEOUT= | 1-8 digits. The default value is 30 minutes. | Sets the timeout value for blocking RECVs, in 0.01 second intervals. Setting 0 results in no timeout. |
| USERID= | 1-8 characters. The default value is None. | Sets the user ID to be used for source data access. This parameter is used with ++SET PASSWORD. If this statement is not coded, access is given with the current credentials for the address space. |
| VPIO= | 1 – 3 digits. The default value is None. | Sets the number of I/O threads. |
| VPNAME= | 1 – 8 characters. The default value is None. | Sets the VPD group name. |
| VPNO= | 1 – 3 digits. The default value is None. | Sets the number of members in the VPD group. |
| VPTO= | 1 – 5 digits. The default value is None. | Sets the group-formation timeout time. |
| WAIT= | ECB | PET. The default value is ECB. | Controls whether the application uses an ECB or a Pause Element Token (PET) to wait for data. For ECB, the driver uses blocking RECV calls and wait on an ECB. For PET, it uses non-blocking RECV calls and use PETs for synchronization. |
The following is a sample IN statement.
//IN DD *
++SET MODE=LOCATE;
SELECT * FROM DB2STAF2;
++SET MODE=MOVE;
SELECT * FROM LUWWRE4 WHERE TM_PERID_ID = 1;
/*