Nonstop Database, HiRDB Version 9 UAP Development Guide
This function collects in an SQL trace file the SQL trace information for an executed UAP.
If an SQL error occurs during UAP execution, the SQL trace information can be used to identify the SQL statement that caused the error.
When the current SQL trace file becomes full, a new file is swapped in and the previous information in that file can be overwritten.
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).
This subsection describes the output destination and file names of SQL traces.
Table 11-1 Whether an X/Open-compliant API (TX_ function) is used and the file names that are assigned
Use of TX_function | File names that are assigned |
---|---|
No | pdsql1.trc and pdsql2.trc |
Yes | pdsqlxxxxx-1.trc and pdsqlxxxxx-2.trc |
The following table lists the SQL trace file names for each library type.
Table 11-2 SQL trace file names for each library type
Library type# | Whether TX_ function is used | PDXATRCFILEMODE environment variable | Client library | |
---|---|---|---|---|
Version earlier than 08-05 | Version 08-05 or later | |||
Regular library | -- | -- | pdsql1.trc or pdsql2.trc | pdsql1.trc or pdsql2.trc |
Single-thread edition of XA library | No | LUMP | pdsql1.trc or pdsql2.trc | pdsql1.trc or pdsql2.trc |
SEPARATE | pdsqlxxxxx-1.trc or pdsqlxxxxx-2.trc | pdsql1.trc or pdsql2.trc | ||
Yes | LUMP | pdsql1.trc or pdsql2.trc | pdsql1.trc or pdsql2.trc | |
SEPARATE | pdsqlxxxxx-1.trc or pdsqlxxxxx-2.trc | pdsqlxxxxx-1.trc or pdsqlxxxxx-2.trc | ||
Multi-thread edition of XA library | No | -- | pdsql1.trc or pdsql2.trc | pdsql1.trc or pdsql2.trc |
Yes | LUMP | pdsql1.trc or pdsql2.trc | pdsql1.trc or pdsql2.trc | |
SEPARATE | pdsqlxxxxx-1.trc or pdsqlxxxxx-2.trc | pdsqlxxxxx-1.trc or pdsqlxxxxx-2.trc |
#
The following table shows the library types:
Library type | Library names in UNIX environment#1 | Library names in Windows environment |
---|---|---|
Regular library | libzclt.sl,ibzclt64.sl, libzclts.sl,libzclts64.sl, libzcltk.sl,libzcltk64.sl, libzcltm.sl,libclt.a, libclt64.a,libclts.a, libclts64.a,libcltk.a, libcltk64.a,libcltm.a |
cltdll.dll,pdcltm32.dll, pdcltp32.dll, pdcltm50.dll, pdcltm71.dll, pdcltm80s.dll, pdcltm64.dll, pdcltm90s.dll, pdcltm90s64.dll |
Single-thread edition of XA library | libzcltx.sl,libzclty.sl, libzcltx64.sl,libzclty64.sl, libzcltxs.sl,libzcltys.sl, libcltxa.a,libcltya.a, libcltxas.a,libcltyas.a |
pdcltx32.dll, pdcltxs.dll,pdcltx64.dll, pdcltxs64.dll |
Multi-thread edition of XA library | libzcltxk.sl,libzcltyk.sl, libzcltxk64.sl, libzcltyk64.sl, libcltxak.a,libclttyak.a |
pdcltxm5.dll, pdcltxm64.dll, pdclto32.dll#2,pdclto64.dll#2 |
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.
[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("hirdb") HOST(h9000vr5) NAMEPORT(20281) FESHOST() SVCGRP() SVCPORT() SRVTYPE() SWAIT(600) CWAIT(0) SWATCH(0) BLKF(1) RDABLKF(-1) LOCKLMT(0) ISLLVL(2) DBLOG(ALL) DFLNVAL(NOUSE) AGGR(1024) DLKPRIO(64) EXWARN(NO) VWOPTMODE(0) LOCKSKIP(NO) CLTGRP(A) DSQLOBJCACHE(YES) 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) NODELAYACK(NO) CURSORLVL(0) TAAPINFPATH() TAAPINFMODE(0) TAAPINFSIZE(409600) JETCOMPATIBLE(NO) SUBSTRLEN() BLKFUPD() ARYERRPOS() CALCMDWAITTIME(0) BLKFERRBREAK(NO) XAAUTORECONNECT(NO) CLTBINDLOOPBACKADDR(NO) STANDARDSQLSTATE() LCKWAITTIME(-1) DDLDEAPRPEXE(NO) 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 |
Operation code | Corresponding SQL statement |
---|---|
ALCR | ALLOCATE CURSOR 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 |
CLIN | INSTALL CLIB |
CLOS | CLOSE statement |
CLRM | REMOVE CLIB |
CLRP | REPLACE CLIB |
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. |
DSET | DEALLOCATE PREPARE statement |
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) |
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# |
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 | AIX |
AIX (64-bit mode) | AIX64 |
Linux | LINUX |
Windows | WIN32 |
HP-UX (IPF) 32-bit mode | HPI32 |
HP-UX (IPF) 64-bit mode | HPI64 |
Linux (EM64T) | LINX64 |
Windows (IPF) 64-bit mode | WINI64 |
Windows (x64) 64-bit mode | WINX64 |
Type4 JDBC driver | Type4 |
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 |
PDCLTM71.DLL | WIN_M71 |
PDCLTM80S.DLL | WIN_M80S |
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 |
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 edition HiRDB client, you use the dir command or the Explorer to check the file update dates/times.
For a UNIX edition 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.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.