SQL trace information is collected by setting values in the PDCLTPATH and PDSQLTRACE environment variables during client environment definition. For details about the client environment definition, see 6.6 Client environment definitions (setting environment variables).

The two SQL trace files in which information is collected are created under a specified directory. The file names that are created depend on whether or not an API (TX_function) conforming to X/Open is used.

Table 10-1 shows the relationship between the TX_function and the files that are created.

Table 10-1 Relationship between the use of an API (TX_function) conforming to X/Open and created SQL trace files

Use of TX_function	Created SQL trace files
No	pdsql1.trc and pdsql2.trc
Yes	pdsqlxxxxx-1.trc and pdsqlxxxxx-2.trc

xxxxx: Process ID during UAP execution

SQL trace information is output after the execution of SQL statements is completed. An example of output of SQL trace information is shown as follows, followed by an explanation.

Output example

[20] [19] [22] ** UAP TRACE (CLT:VV-RR(Mmm dd yyyy) SVR:VV-RR US) WIN32(WIN32) ** USER APPLICATION PROGRAM FILE NAME : XXXXXXXX [1] USERID : YYYYYYYY [2] UAP START TIME : YYYY/MM/DD HH:MM:SS [3] UAP ENVIRONMENT : [4] LANG(ja_JP.SJIS) USER("YYYYYYY") HOST(dcm3500) NAMEPORT(20281) FESHOST( ) SVCGRP( ) SVCPORT( ) SRVTYPE( ) SWAIT(600) CWAIT(0) SWATCH(0) BLKF(1) LOCKLMT(0) ISLLVL(2) DBLOG(ALL) DFLNVAL(NOUSE) AGGR(1024) DLKPRIO(64) EXWARN(NO) VWOPTMODE(0) LOCKSKIP(NO) CLTGRP(A) PLGIXMK(NO) CLTRCVPORT(5000) CLTRCVADDR(192.134.35.4) PLGPFSZ(8192) PLGPFSZEXP(8192) SPACELVL(-1) STJTRNOUT( ) OPTLVL("SELECT_APSL","RAPID_GROPING") ADDITIONALOPTLVL("COST_BASE_2","APPLY_HASH_JOIN") UAPREPLVL( ) REPPATH( ) TRCPATH( ) IPC(MEMORY) SENDMEMSIZE(16) RECVMEMSIZE(32) HASHTBLSIZE(128) CMMTBFDDL(NO) PRPCRCLS( ) SQLTRCOPENMODE(SQL) AUTOCONNECT(ON) CWAITTIMEWRNPNT(-1) TCPCONOPT(0) WRTLNFILSZ(-1) WRTLNCOMSZ(1024) WRTLNPATH( ) UAPENVFILE( ) TP1SERVICE(NO) AUTORECONNECT(NO) RCCOUNT(0) RCINTERVAL(0) KALVL(0) KATIME(0) CLTCNVMODE(NOUSE) PRMTRC(YES) PRMTRCSIZE(256) BESCONHOLD() BESCONHTI(-1) BLKBUFFSIZE(0) BINARYBLKF(NO) FORUPDATEEXLOCK(NO) CNSTRNTNAME() SQLTEXTSIZE(4096) RCTRACE(-1) FESGRP() NBLOCKWAITTIME(0) CONNECTWAITTIME(300) DBBUFLRU(YES) UAPEXERLOGUSE() UAPEXERLOGPRMSZ() HJHASHINGMODE(TYPE1) DDLDEAPRP(NO) DELRSVWDFILE() HATRNQUEUING() ODBSPLITSIZE(100) CURSORLVL(0) TAAPINFPATH() TAAPINFMODE(0) TAAPINFSIZE(409600) JETCOMPATIBLE(NO) SUBSTRLEN() CONNECTION STATUS : [5] CURHOST(dcm3500) CURPORT(4439) SRVNAME(fes1) CNCTNO(1) SVRPID(8945) CLTPID(9155) CLTTID( ) CLTCNCTHDL(0x0) [6] [7] [8] [9] [10] [11] [12] [13] [14] [15] [16] [23] CNCT CLPID CLTID NO OP SEC SQL SQL START-TIME END-TIME OP EXEC-TIME NO CODE NO CODE WARN TION ---- ----- ----- -- ---- --- ---- ----- ------------ ------------ ---- ----------- 1 9155 0 1 CNCT 0 0 WC040 16:03:55.720 16:03:58.080 0001 2356125 1 9155 0 2 AUI2 1 0 -0000 16:03:58.630 16:03:59.400 M000 769651 *SQL* INSERT INTO STOCK(GNO,GNAME,PLAN,PRICE,QUANTITY,DISCOUNT) VALUES(?,?, .........17 ?,?,?,?) ........................................................................17 1 9155 0 3 SET 2 0 -0000 16:04:00.820 16:04:01.540 M000 719825 *SQL* SELECT GNO,GNAME,PLAN,PRICE,QUANTITY,DISCOUNT FROM STOCK ......................17 1 9155 0 4 OPEN 2 0 -0000 16:04:02.090 16:04:02.800 M000 709123 1 9155 0 5 FETC 2 -204 -0000 16:04:03.080 16:04:03.790 M000 708902 1 9155 0 6 SET 2 0 W8800 16:04:04.060 16:04:04.830 M000 765147 *SQL(AUTHID)* INSERT INTO TBL01 VALUES('12345',12345) ...........................17 1 9155 0 7 SAUT 0 0 -0000 16:04:04.834 16:04:04.835 M000 912 *USER* hirdb01 ..................................................................18 1 9155 0 8 AUI2 3 0 -0000 16:05:05.110 16:05:05.121 M000 9456 *SQL* INSERT INTO TBL01 VALUES(?,100) ...........................................21 *PARAM* NO= 1 COD=c5 XDIM= 1 SYS= 0 LEN= 15 IND= 0 .......21 DATA=30 35 2d 30 35 00 00 00 00 00 00 00 00 00 00 *05-05.........* ..21 1 9155 9 DISC 0 0 -0000 16:05:55.110 16:05:56.660 M004 1547893

1. UAP name: Displays the name specified in the PDCLTAPNAME environment variable.
2. Authorization identifier: Displays the authorization identifier of the user who executed the UAP.
3. UAP start time: Displays the time at which execution of the UAP started.
4. UAP execution environment: Displays the values of the environment variables when the UAP was executed.
5. UAP execution status: Displays the status of the connection with the server when the UAP was executed:
   
   • CURHOST: Connection-destination host name
   • CURPORT: Connection port number
   • SRVNAME: Front-end server name or single-server name
   • CNCTNO: Connection serial number
   • SVRPID: Connected server process status
   • CLTPID: UAP process number
   • CLTTID: UAP thread number
   • CLTCNCTHDL: Connection handle
   If information cannot be obtained, an invalid value may be displayed (Windows).
   
6. Connection serial number: Displays the connection serial number. Connection serial numbers are assigned sequentially each time the server accepts CONNECT.
7. Displays the process number of the UAP.
   If the correct process number cannot be obtained, an invalid value may be displayed (Windows).
   
8. UAP thread number: Displays the UAP thread number when the UAP is running in a multi-thread environment. Displays 0 if the UAP is not running in a multi-thread environment. A thread number that cannot be allocated can sometimes be displayed as an invalid value.
9. SQL counter: Displays the SQL counter values. Each time an SQL statement is accepted, the counter value is incremented (from 1 through 999999, after which the counter value returns to 1).
10. Operation code: Displays the operation code that corresponds to each SQL statement.
    The following table shows the SQL statements that correspond to the displayed operation codes.
    

    Operation code	Corresponding SQL statement
    AUI2	DELETE statement (static SQL), INSERT statement (static SQL), UPDATE statement (static SQL), LOCK statement (static SQL), PURGE TABLE statement (static SQL), single-row SELECT statement (static SQL), FREE LOCATOR statement (static SQL)
    AUI3	Assignment statement (static SQL)
    AUX	EXECUTE statement
    AUXI	EXECUTE IMMEDIATE statement, all definition SQL statements
    AUXO	EXECUTE statement (INTO specified)
    CALL	CALL statement
    CLOS	CLOSE statement
    CMIT	COMMIT statement
    CNCT	CONNECT statement
    CPRP	Commit prepare*
    DESC	DESCRIBE statement (OUTPUT specified)
    DEST	DESCRIBE TYPE statement
    DISC	DISCONNECT statement, COMMIT statement (RELEASE specified)
    DISR	ROLLBACK statement (RELEASE specified)
    DIST	Disconnect + Tran Check*
    DSCM	Used by the system.
    DSPR	Used by the system.
    DSRL	Used by the system.
    FETC	FETCH statement
    GETD	GET DIAGNOSTICS
    HVAR	DESCRIBE statement (INPUT specified)
    JARI	INSTALL JAR
    JARR	REPLACE JAR
    JARU	REMOVE JAR
    OPEN	OPEN statement (dynamic SQL)
    OPN2	OPEN statement (static SQL)
    OPNR	OPEN statement (dynamic SQL (multiple cursors))
    RENV	Used by the system.
    RNCN	CONNECT statement (TO specified)
    RNDS	DISCONNECT statement (TO specified)
    RNSC	SET CONNECTION statement
    ROLL	ROLLBACK statement
    RSDC	DESCRIBE statement (OUTPUT and RESULT SET specified)
    RSFT	FETCH statement (RESULT SET specified)
    RSCL	CLOSE statement (RESULT SET specified)
    SAUT	SET SESSION AUTHORIZATION statement
    SET	PREPARE statement
    SINF	Used by the system.
    SOPT	Used by the system.
    SVLS	Used by the system.
    THRE	Used by the system.
    THSU	Used by the system.
    TRCK	Used by the system.
    TRC2	Used by the system.
    TRST	Used by the system.
    TSCM	Used by the system.
    TSRL	Transfer Rollback*
    TSPR	Transfer Prepare*
    ALCR	ALLOCATE CURSOR statement
    DSET	DEALLOCATE PREPARE statement

    ^* Applicable only when the XA interface is used.

    
    
11. Section number: Displays a number for verifying SQL statement correspondence; this number is assigned automatically by the SQL preprocessor.
12. SQLCODE: Displays the SQLCODE that occurs as a result of SQL statement execution.
13. SQLWARN: Displays warning information (in hexadecimal). Starting from the left, one bit each is allocated to warning information SQLWARN0 through SQLWARNF. A 16-bit value is obtained by setting each bit to 1 if the warning flag is set and to 0 if it is not set. This obtained value is displayed as a 4-digit hexadecimal number.
    

    W is displayed at the beginning if at least one warning flag is set; - is displayed if no warning flags are set.

    Example 1
    

    
    

    Example 2
    

    
    

14. SQL statements execution request receipt time: Displays the time at which the SQL execution request was received (in HH:MM:SS:mmm format).
15. SQL statement execution request termination time: Displays the time at which the SQL statement execution request was terminated (in HH:MM:SS:mmm format).
16. Information used by the system: Displays the information used by the system. If the first byte is M, memory is used for process-to-process communication. The other part of the information is used by the HiRDB developer for maintenance purposes.
17. SQL statement: Displays the SQL statement, but only when the operation code is SET, AUXI, AUI2, or OPN2.
    The maximum SQL statement length that can be output is 4,096 bytes; any excess is truncated. If the -A option was specified during preprocessing or the /A option was used during preprocessing to specify the authorization identifier to be assumed when the authorization identifier in the SQL statement is omitted, *SQL* is displayed as *SQL (assumed-authorization-identifier)*.
    
18. New user identifier: Displays a new user identifier if the user identifier was changed during a single connection. This information is also displayed if the user identifier change operation fails.
19. Platform for UAP:
    

    Platform	Character string to be displayed
    HP-UX 11.0	HP32
    HP-UX 11.0 (64-bit mode)	HP64
    Solaris	SOL
    Solaris (64-bit mode)	SOL64
    AIX 5L	AIX
    AIX 5L (64-bit mode)	AIX64
    Linux	LINUX
    Windows	WIN32
    HP-UX (IPF) 32-bit mode	HPI32
    HP-UX (IPF) 64-bit mode	HPI64
    Linux (IPF)	LINI64
    Linux (EM64T)	LINX64
    Windows Server 2003 (IPF)	WINI64
    Windows (x64) 64-bit mode	WINX64
    Type4 JDBC driver	Type4

20. Library creation date: Displays the creation date of the linked library in the following format:
    Mmm: Month (first three letters of the month in English with the first letter in upper case). For example, June is displayed as Jun.
    dd: Date
    yyyy: Year
    
21. Parameter trace: Displays input parameter information, output parameter information, and retrieved data when PDPRMTRC=YES, IN, OUT, or INOUT is specified in the client environment definitions.
    The parameter information data is displayed up the length specified in PDPRMTRCSIZE (or 256 bytes if omitted), and any excess part is discarded. For details, see (4) Parameter trace output examples.
    

    NO

    Parameter number

    COD

    Data type code

    XDIM

    Number of array elements

    SYS

    Length of one element, including gaps

    LEN

    Data length

    IND

    Value of indicator variable

    ARRAY NUM

    Number of elements in repetition array

    ROW NUM

    Number of execution rows in SQL that uses embedded variables in an array

    DATA

    Data (dump format)

22. Linked library name
    

    Library name	Displayed characters
    libzclt.sl, libclt.a	UNIX, UNIX_32
    libzclts.sl, libclts.a	UNIX_S, UNIX_32S
    libzcltm.sl, libcltm.a	UNIX_M, UNIX_32M
    libzcltk.sl, libcltk.a	UNIX_K, UNIX_32K
    libzcltx.sl, libcltxa.a	UNIX_XA, UNIX_XA_32
    libzcltxs.sl, libcltxas.a	UNIX_XA_S, UNIX_XA_32S
    libzcltxm.sl, libcltxam.a	UNIX_XA_M, UNIX_XA_32M
    libzcltxk.sl, libcltxak.a	UNIX_XA_K, UNIX_XA_32K
    libzclt64.sl, libclt64.a	UNIX_64
    libzcltk64.sl, libcltk64.a	UNIX_64K
    libzclts64.sl	UNIX_64S
    libzcltx64.sl, libzclty64.sl	UNIX_XA_64
    libzcltxk64.sl, libzcltyk64.sl	UNIX_XA_64K
    libzcltxs64.sl, libzcltys64.sl	UNIX_XA_64S
    CLTDLL.DLL	WIN_32
    PDCLTM32.DLL	WIN_M32
    PDCLTM50.DLL	WIN_M50
    PDCLTP32.DLL	WIN_P32
    PDCLTX32.DLL	WIN_XA_32
    PDCLTXM.DLL	WIN_XA_32M
    PDCLTXS.DLL	WIN_XA_32S
    PDCLTXM5.DLL	WIN_XA_50M
    PDCLTM64.DLL	WIN_M64
    PDCLTX64.DLL	WIN_XA_64
    PDCLTXM64.DLL	WIN_XA_64M
    PDCLTXS64.DLL	WIN_XA_64S
    PDJDBC2.JAR	Type4

23. SQL runtime: Displays the SQL runtime in microseconds when PDSQLEXECTIME=YES is specified in the client environment definitions.

If the SQL trace file becomes full while SQL trace information is being output, HiRDB stops writing to that file and outputs SQL trace information to another SQL trace file. Any information that already exists in the switched-in SQL trace file is overwritten in chronological order by the new SQL trace information. To prevent that information from being lost, copy the contents of the SQL trace file into a backup file whenever execution of a UAP is completed.

To determine the SQL trace file that is being used currently, check the most recent update dates/times of the files. The SQL trace file that was updated most recently is the current file.

For a Windows version HiRDB client, you use the dir command or the Explorer to check the file update dates/times.

For a UNIX version HiRDB client, you use the OS's 1s -1 command to check the file update dates/times.

Output examples of representative parameter traces are shown below.

(a) INSERT statement (with null value and repetition column)

Explanation

This is an output example of parameter trace information when INTEGER and VARCHAR(10) repetition column (five elements) values are inserted with the INSERT statement. The values are output in the sequence in which the ? parameters are specified.

1. For input parameters, *INPRM* is displayed. However, when PDPRMTRC=YES, *PARAM* is displayed.
2. For a repetition column, the number of repeated elements is displayed in ARRAY NUM.
3. The number before each DATA clause is the indicator variable of each element in the repetition column.
4. The number in parentheses in each DATA clause is the repetition column element number.
5. For VARCHAR-type data, the first 2 bytes of DATA is the data length area (the first 4 bytes for BINARY-type data, and the first 8 bytes for BLOB-type data). When PDPRMTRC is YES, the size of the output information is the sum of the defined length and the data area length. When PDPRMTRC is IN, OUT, or INOUT, the size of the output information is the sum of the actual data length and the data area length.
6. If the indicator variable is a negative value, only the information up to DATA= is displayed.
7. If the data extends beyond one line, --- SAME x LINES --- (x is the number of lines) is output. However, when PDPRMTRC=YES, all data is output.

(b) Single-row SELECT statement

Explanation

This is an output example of parameter trace information when PDPRMTRC=INOUT is specified. The retrieval data information is output first in retrieval item sequence, and the input parameter information is output later in the specification sequence.

1. This is the retrieval data information. This information is not output when PDPRMTRC=IN. When PDPRMTRC=YES, *PARAM* is displayed instead of *OUTPM*.
2. This is the input parameter information. This information is not output when PDPRMTRC=OUT. When PDPRMTRC=YES, *PARAM* is displayed instead of *INPRM*.

(c) Stored procedure execution (CALL statement)

Explanation

1. This is the IN parameter. When PDPRMTRC=OUT, this information is not output.
2. This is the input parameter of the INOUT parameter. However, the contents of the DATA clause become output data.
3. This is the OUT parameter. This information is not output when PDPRMTRC=IN or YES.
4. This is the output parameter of the INOUT parameter. This information is not output when PDPRMTRC=IN or YES.

(d) Retrieval (FETCH statement)

Explanation

This is an output example of parameter trace information for the FETCH statement. A parameter trace is not output when PDPRMTRC=IN or YES.

1. If the SQLCODE of the FETCH statement is a value other than 0, a parameter trace is not output.

(e) Retrieval (FETCH facility using arrays)

Explanation

This is an output example of parameter trace information for the FETCH facility using arrays. A parameter trace is not output when PDPRMTRC=IN or YES.

1. ROW NUM displays the number of array elements (number of retrieval rows).
2. The number before each DATA clause is the indicator variable of each array element.
3. The number in parentheses in each DATA clause is the array element number.
4. If the SQLCODE of the FETCH statement is a value other than 0, parameter trace information is output for the number of rows returned from the server.