DB2 Server for VSE & VM: Database Administration

Appendix E. SQLGLOB Parameters (VSE Only)

DB2 Server for VSE stores certain environmental parameters in a VSAM file called "SQLGLOB." ISQL, DBSU and the preprocessors retrieve the CHARNAME and DBCS values from this SQLGLOB file. The online and batch Resource Adapters also access this file to determine certain environmental parameters as they communicate with a remote application server.

The SQLGLOB VSAM file will hold both GLOBAL and USER parameters. There is one set of global SQLGLOB parameters, which is the system-wide default values. These global parameters are initially set with the IBM-supplied default values during installation using the ARISGDEF procedure and subsequently updated using the DSQG transaction. A CICS user can choose to override the global SQLGLOB parameters by setting up his or her own user SQLGLOB parameters. This is done by executing the DSQU transaction. There is one set of user SQLGLOB parameters for every CICS user who executed the DSQU transaction. |A batch user can choose to override the global SQLGLOB parameters by |setting up their own user SQLGLOB parameters. This is done by executing |the program ARIRBGUD (JCL: ARISBGUD.Z) and specifying the Update |(U) command and a user ID. The |ARIRBGUD program can also be used |to update the global SQLGLOB parameters, to query one of the user's parameters or all of the user's parameters, or to delete a user's set of parameters.

The SQLGLOB VSAM file is defined to the system and initially updated with the IBM-supplied default global SQLGLOB parameter values during product installation.

The SQLGLOB parameters and their initial IBM-supplied global default values are described below:

QryBlksize

Specifies the block size used to return rows of data when DRDA blocking is used to perform FETCHes. The number is specified in denominations of 1K and can range anywhere between 1K and 32K.

This option is only used when the application requester is communicating with a remote application server.

The IBM-supplied global default QryBlksize is 8K.

CHARNAME

Specifies the character set name, which determines the CCSID values for CCSIDSBCS, CCSIDMIXED and CCSIDGRAPHIC used by the application requester, and determines how ISQL and the preprocessors fold characters from lowercase to uppercase. Its value must be a valid character set name, such as those found in the CHARNAME column of the SYSTEM.SYSCCSIDS table.

This value is used by |ISQL, DBSU, and the preprocessors instead of the value currently found in the SYSTEM.SYSOPTIONS table.

The IBM-supplied global default CHARNAME is INTERNATIONAL.

DBCS

Specifies whether DBCS character handling of SO/SI pairs is done or not. This value is used by ISQL, |DBSU, and the preprocessors instead of the value currently found in the SYSTEM.SYSOPTIONS table.

YES: Specifies that error checking is done on DBCS data by the preprocessors, |DBSU and ISQL. If double byte characters are to be used, DBCS must be set to YES.
NO: Specifies that error checking is not done on DBCS data by the |preprocessors, DBSU and ISQL.

The IBM-supplied global default DBCS is NO.

SYNCPOINT

Specifies how commits or rollbacks are to be coordinated by the CICS/VSE syncpoint manager.

1: Specifies a one-phase commit is to be done. In this case, the CICS/VSE sync point manager is not involved and unprotected APPC conversations are used.
2: Specifies a two-phase commit is to be done. In this case, protected APPC conversations will be used to connect to the DRDA server and the CICS/VSE sync point manager will be used to coordinate two-phase commits. If a user is updating a remote server and other CICS resources which participate in two-phase commit within a logical unit of work, SYNCPOINT must be set to 2.

This option is only used when the |online CICS application requester is communicating with a remote |application server through SNA.

The IBM-supplied global default SYNCPOINT is 1.

TRACERA

Specifies a two digit number (nn) which specifies the parts of the Batch and Online Resource Adapter that are to be traced and the level of the trace. Trace data is collected only when the application is connected to a remote server. The positional digits correspond to the |following Resource Adapter subcomponents and functions:

|Resource Adapter control flow
Communications.

0: Tracing is turned off.
1: Tracing is done in limited detail.
2: Tracing is done in greater detail.

TRACERA is ignored on local connections.

The IBM-supplied global default TRACERA is 00.

TRACEDRRM

Specifies a four digit number (nnnn) which specifies the parts of the DRRM component that are to be traced and the level of the trace. Trace data is collected only when the application is connected to a remote server. The positional digits correspond to the following DRRM subcomponents and functions:

Parser
Generator
Dictionary
RDIIN Manager.

0: Tracing is turned off.
1: Tracing is done in limited detail.
2: Tracing is done in greater detail.

TRACEDRRM is ignored on local connections.

The IBM-supplied global default TRACEDRRM is 0000.

TRACECONV

Specifies a 1 digit number (n) which specifies that the data conversion component is to be traced and the level of the trace. Trace data is collected only when the application is connected to a remote server.

0: Tracing is turned off.
1: Tracing is done in limited detail.
2: Tracing is done in greater detail.

TRACECONV is ignored on local connections.

The IBM-supplied global default TRACECONV is 0.

|Communications Protocol

|Specifies which network access method (SNA or TCP/IP) to use for remote |connections. |

|S: |Specifies that SNA is used for the connection to the remote application |server. In this case, the remote application server entry in the DBNAME |Directory should contain SNA routing information (SYSID and REMTPN). S |is the default for this field.
|T: |Specifies that TCP/IP is used for the connection to the remote application |server. In this case, the remote application server entry in the DBNAME |Directory should contain TCP/IP routing information (TCPPORT and TCPHOST, or |TCPPORT and IPADDR). |

|This option is only used when the online CICS application requester is |communicating with a remote application server. Note that Batch |Applications must always use TCP/IP to communicate with a remote |server.

|The IBM-supplied global default COMMUNICATIONS PROTOCOL is SNA.

Transactions for Updating SQLGLOB Parameters

This section describes the various CICS transactions that a user can use to manage parameters stored in the SQLGLOB VSAM file.

DSQG - Update global SQLGLOB Parm Transaction

The DSQG transaction is a CICS transaction which updates a subset of the global SQLGLOB parameters.

This must be defined as a secured transaction. That is, this transaction must be defined with a TRANSEC value greater than 1, so that it cannot be initiated by any user on the CICS system. Only authorized CICS users should be allowed to invoke this transaction.

This transaction has |five parameters as shown in the following syntax diagram. See Appendix E, SQLGLOB Parameters (VSE Only) for more information on these DSQG parameters.

>>-DSQG----+-,---------+---+-,-----------+---+-,-----+---------->
           '-CHARNAME,-'   '-QryBlksize,-'   '-DBCS,-'
 
>-----+-,----------+---+--------------------------+------------><
      '-SYNCPOINT,-'   '-Communications Protocol--'

Each time the DSQG transaction is executed, the global SQLGLOB parameters are replaced as follows:

If the user specifies a parameter on the DSQG transaction, that parameter value is used.
If a parameter is not specified on the DSQG transaction, the current global SQLGLOB parameter is used.

|The global SQLGLOB parameters remain in effect until they are |explicitly changed. The global SQLGLOB parameters can be changed by |invoking the DSQG transaction and specifying the new parameters, or by |invoking program ARIRBGUD (the SQLGLOB File Batch Update/Query Program) and |specifying the Update (U) command with the user ID *SYSDEF*.

DSQU - Update user SQLGLOB Parm Transaction

The DSQU transaction is a CICS transaction which initializes the SQLGLOB parameters for the signed-on user ID. The user SQLGLOB parameters, like the global SQLGLOB parameters, are stored in the SQLGLOB VSAM file.

This transaction has |eight parameters, as shown in the following syntax diagram. See Appendix E, SQLGLOB Parameters (VSE Only) for more information on these DSQU parameters.

>>-DSQU----+-,---------+---+-,-----------+---+-,-----+---------->
           '-CHARNAME,-'   '-QryBlksize,-'   '-DBCS,-'
 
>-----+-,----------+---+-,--------+---+-,----------+------------>
      '-SYNCPOINT,-'   '-TRACERA,-'   '-TRACEDRRM,-'
 
>-----+-,----------+---+--------------------------+------------><
      '-TRACECONV,-'   '-Communications Protocol--'

Each time the DSQU transaction is executed, the user SQLGLOB parameters are replaced. When a user reissues the DSQU transaction, the parameter value is established as follows:

If the user specifies a parameter on the DSQU transaction, that parameter value is used.
If a parameter is not specified on the DSQU transaction, the current user SQLGLOB parameter is used. That is, the default is the value used on the most recent DSQU transaction. However, if the user SQLGLOB parameter does not exist, the global SQLGLOB parameter is used.

|The user SQLGLOB parameters remain in effect until they are |explicitly changed or until they are explicitly deleted through a subsequent |DSQD transaction. The user SQLGLOB parameters can be changed by |invoking the DSQU transaction and specifying the new parameters or by invoking |the program ARIRBGUD (SQLGLOB File Batch Update/Query Program) and specifying |the Update (U) command with the appropriate user ID and new parameters. |The user SQLGLOB parameters can be deleted by invoking the DSQD transaction or |by invoking the program ARIRBGUD and specifying the Delete (D) command with |the appropriate user ID.

All DRDA |connections initiated by online CICS transactions, except those initiated by ISQL, CBND, or any task that was started by the EXEC CICS START command, will use the signed-on user's SQLGLOB parameters, if they exist. If they do not exist, these DRDA connections will use the global SQLGLOB parameters.

ISQL, CBND, and any task that was started by the EXEC CICS START command will use the global SQLGLOB parameters for DRDA connections regardless of who is signed on, with the following exceptions:

ISQL uses the user DBCS parameter to determine whether DBCS character handling is required or not.
ISQL uses the user CHARNAME parameter to get the folding table to fold input from the terminal from lowercase to uppercase, but it uses the global CHARNAME for CCSID data conversion.

|All DRDA connections initiated by VSE batch application programs |will use the SQLGLOB parameters (if they exist) of the user ID specified on |the SQL CONNECT statement. If they do not exist, these DRDA connections |will use the global SQLGLOB parameters.

DSQQ - Query SQLGLOB Parm Transaction

The DSQQ transaction is a CICS transaction which displays all the SQLGLOB parameters. Which version of the SQLGLOB parameters (user or global) is displayed depends on whether or not the userid parameter is specified on the DSQQ command.

This transaction has one parameter, as shown in the following syntax diagram:

>>-DSQQ----+---------+-----------------------------------------><
           '-userid--'

userid: Specifies the user ID whose user SQLGLOB parameters are to be displayed.

If the userid parameter is specified and the user SQLGLOB parameters of the specified userid exist, DSQQ will display the user SQLGLOB parameters.

If the userid parameter is omitted, DSQQ will display the global SQLGLOB parameters.

DSQD - Delete user SQLGLOB Parm Transaction

The DSQD transaction is a CICS transaction which deletes a signed-on user ID's user SQLGLOB parameters.

This transaction has no parameters, as shown in the following syntax diagram:

>>-DSQD--------------------------------------------------------><

After the signed-on user's user SQLGLOB parameters are deleted, any subsequent DRDA connections done on behalf of this signed-on user ID will use the global SQLGLOB parameters.

|Batch Program to Update/Query the SQLGLOB File

|If a "CONNECT user ID" is needed for a remote |server, but the user ID does not exist as a CICS user ID, then a batch program |and JCL are supplied to allow the SQLGLOB file to be updated for any user |ID. This stand-alone program, ARIRBGUD, allows a new user ID to be |inserted or an existing user ID to be updated, deleted or queried based on the |input given to the program. Input for this program is provided from |SYSIPT "cards" (80 byte records).

|Table 37 describes the layout of the input for ARIRBGUD:
|

|Table 37. ARIRBGUD Input Layout

Description
Start -- End Column #s Field Name Usage
1 - 1 COMMAND 'Q' = query, 'D' = delete, or 'U' = update. This field is mandatory.
3 - 10 USERID This is the user ID in the SQLGLOB file that is to be operated upon. This can be '*' if COMMAND is 'Query'. This field is mandatory with an 8 byte maximum length.
12 - 29 CHARNAME Character Set Name; it determines CCSID values and folding (18 bytes maximum).
31 - 32 QRYBLKSIZE Block size used for Blocked Fetches, in integral 'K' bytes, from 1 to 32.
34 - 34 DBCS 'Y' if DBCS is allowed. 'N' if DBCS is not allowed.
36 - 36 SYNCPOINT '1' or '2', for one- or two-phase COMMIT (DRDA 1 or DRDA 2). This field is only used by the CICS Application Requester for remote DRDA connections.
38 - 39 TRACERA Resource Adapter Trace flags, two digits, each either '0', '1' or '2'.
41 - 44 TRACEDRRM DRRM Trace flags, four digits, each either '0', '1' or '2'.
46 - 46 TRACECONV CONV Trace Flags, 1 digit, either '0', '1' or '2'.
48 - 48 COMMPROTO Communications Protocol to be used for Remote database access: 'S'=SNA or 'T'=TCP/IP. Only used by CICS , not Batch.

Description
Start -- End Column #s	Field Name	Usage
1 - 1	COMMAND	'Q' = query, 'D' = delete, or 'U' = update. This field is mandatory.
3 - 10	USERID	This is the user ID in the SQLGLOB file that is to be operated upon. This can be '*' if COMMAND is 'Query'. This field is mandatory with an 8 byte maximum length.
12 - 29	CHARNAME	Character Set Name; it determines CCSID values and folding (18 bytes maximum).
31 - 32	QRYBLKSIZE	Block size used for Blocked Fetches, in integral 'K' bytes, from 1 to 32.
34 - 34	DBCS	'Y' if DBCS is allowed. 'N' if DBCS is not allowed.
36 - 36	SYNCPOINT	'1' or '2', for one- or two-phase COMMIT (DRDA 1 or DRDA 2). This field is only used by the CICS Application Requester for remote DRDA connections.
38 - 39	TRACERA	Resource Adapter Trace flags, two digits, each either '0', '1' or '2'.
41 - 44	TRACEDRRM	DRRM Trace flags, four digits, each either '0', '1' or '2'.
46 - 46	TRACECONV	CONV Trace Flags, 1 digit, either '0', '1' or '2'.
48 - 48	COMMPROTO	Communications Protocol to be used for Remote database access: 'S'=SNA or 'T'=TCP/IP. Only used by CICS , not Batch.

|Notes:

|The COMMAND and USERID are always required.
|For the Query COMMAND only, the user ID can be specified as '*', |which means display all user IDs in the SQLGLOB file.
|For a Query or Delete COMMAND, all other fields are ignored.
|For an Update COMMAND, fields that are left blank remain unchanged in an |existing record for the user ID. If the user ID does not already have |an existing record in SQLGLOB, then the System Default record values are |used.
|The System Default record (user ID = X'FF's) CANNOT be deleted by |this program. It CAN be updated or queried. Considering this |user ID cannot be displayed, it is displayed as the string "*SYSDEF*", and |this string must be used for the Update or Query COMMAND as the "CONNECT User |ID". |

|The following is an example of the SYSLST output from the program ARIRBGUD, |with a default of 120 print positions per print line. Input records are |shown in bold italics. Note that the first byte of the listing is a |printer carriage control character.

|

1ARIRBGUD - Batch Query/Update of DB2 for VSE SQLGLOB File 1999/12/31 23:59:59 0 CCSID CCSID CCSID QRYBLK SYNC ----TRACE--- COMM COMMAND USER CHARNAME SBCS MIXED DBCS SIZE DBCS POINT RA DRRM CONV PROTO 0----> Q UUUUUUUU QUERY uuuuuuuu cccccccccccccccccc sssss sssss sssss 12K Y 1 00 0000 0 TCPIP 0----> U UUUUUUUU 32 N 2 11 2222 S UPDATE UUUUUUUU cccccccccccccccccc sssss sssss sssss 32K N 2 11 2222 0 SNA 0----> D UUUUUUUU DELETE UUUUUUUU 0----> Q UUUUUUUU ARI0485I The user SQLGLOB parameters for user UUUUUUUU do not exist. 0----> U BADPARM 99 ARI0494E Invalid input parameter entered. Parameter = QRYBLKSIZE. 0----> X JUNK STUFF ARI4599E Invalid COMMAND given, must be 'Q'(Query), 'U'(Update) or 'D'(Delete). 0----> D *SYSDEF* ARI4598E You can not DELETE the system default record from the SQLGLOB file. 0----> Q * ----> List of all Users in the SQLGLOB file: QUERY uuuuuuuu cccccccccccccccccc sssss sssss sssss bbbbb d s tt tttt t ppppp QUERY uuuuuuuu cccccccccccccccccc sssss sssss sssss bbbbb d s tt tttt t ppppp ... ... ... QUERY *SYSDEF* cccccccccccccccccc sssss sssss sssss bbbbb d s tt tttt t ppppp

|The following is an example of the SYSLST output from ARIRBGUD, because of |a JCL error or because the file has never been created:

|

1ARIRBGUD - Batch Query/Update of DB2 for VSE SQLGLOB File 1999/12/31 23:59:59 0ARI0487E The SQLGLOB file does not exist.

|The JCL to execute the program ARIRBGUD (SQLGLOB File Batch |Update/Query Program) can be found in the IBM-supplied job |ARISBGUD.Z.

|Using Online and Batch Resource Adapter Tracing

|The online (CICS) and batch Resource Adapter tracing is used for problem |analysis. You would normally not turn tracing on unless requested to do |so by IBM Support. The Online Resource Adapter trace output can be |directed to a tape or a disk file, which must be defined in the CICS start up |JCL and predefined in the CICS Destination Control Table. See the DB2 Server for VSE Program Directory for details about setting up the Destination Control Table. The batch |Resource Adapter trace can only be directed to a tape file, which must be |defined in the batch job JCL. Tracing is only performed for application |statements that access remote DRDA servers.

|Tracing is activated by updating the SQLGLOB file record for the SQL |CONNECT statement user IDs (or the default Online user IDs). A |'1' or '2' character placed in the TRACERA, TRACEDRRM or |TRACECONV component field activates tracing for the respective |subcomponents. A '0' character in this field deactivates |tracing. Care should be taken when updating the trace fields in the |Global Default user ID because this would cause tracing to be activated for |ALL users who do not have a record in the SQLGLOB file; also, under CICS, |all trace data is intermingled in the single trace output file. You can |use the DSQU CICS transaction or the ARIRBGUD program (described in Batch Program to Update/Query the SQLGLOB File) to update the SQLGLOB file records.

|Online Trace File JCL

|The following is JCL that must be placed in the CICS start up JCL to |define the trace output file: |

|For a tape file:

|// ASSGN SYS018,181
|// TLBL ARITRAC,'name of trace file'

|For a disk file:

|// ASSGN SYS018,DISK,VOL=volxxx,SHR
|// DLBL ARITRAC,'name of trace file',0,SD
|// EXTENT SYS018,volxxx,n,n,nnn,nn

|Batch Trace File JCL

|The following is JCL that must be placed in the batch job JCL to define |the trace output file:

|// ASSGN SYS005,181
|// TLBL ARITRAC,'name of trace file'

|Formatting the Online or Batch Trace File

|After the trace is finished, you can invoke the trace formatting |utility that comes with the DB2 Server for VSE system. The above JCL |must also be used when the trace file is used as input to the trace formatter |utility, except 'SYS004' must be used instead of 'SYS005' |or 'SYS018' when your trace file is on tape. |'SYS018' must be used when your Online trace file is on disk. |Note that the Batch trace file cannot be on disk. See |"Formatting DB2 Server for VSE Trace Output" in DB2 Server |for VSE & VM Operation.

|Before the Online Resource Adapter trace file can be formatted, it must be |closed using the following CICS command:

|CEMT SET QUEUE(ARIT) DISABLED CLOSED

|Before the batch Resource Adapter trace file can be formatted, it must be |closed using the following JCL statement "after" the batch job step being |traced:

|// MTC WTM,SYS005,2

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]