IFCID 002 - SQL Call Data
This topic shows detailed information about Record Trace - IFCID 002 - SQL Call Data
.
Record Trace - IFCID 002 - SQL Call Data
The field labels shown in the following sample layout of Record Trace - IFCID 002 - SQL Call Data
are described in the following section.
SQL CALL DATA
SELECT ..........: 12158358 INSERT ..........: 2317413 UPDATE ..........: 3308351
DELETE ..........: 34478 TYPE 1 INSRT ALG.: 248658 PREPARE .........: 30540
DESCRIBE ........: 356729 TYPE 2 INSRT ALG.: 0
OPEN ............: 138162 CLOSE ...........: 126456 FETCH ...........: 74137826
COMMENT ON ......: 0 LOCK TABLE ......: 0 GRANT ...........: 0
REVOKE ..........: 0 INCREMENTAL BINDS: 46353 LABEL ON ........: 0
DESCRIBE TABLE ..: 0 CONNECT TYPE 1 ..: 0 CONNECT TYPE 2 ..: 15
RELEASE .........: 0 ASSOCIATE LOCATOR: 5 ALLOCATE CURSOR .: 5
RENAME TABLE ....: 0 HOLD LOCATOR ....: 0 FREE LOCATOR ....: 0
MERGE ...........: 0 TRUNCATE TABLE ..: 0 RENAME INDEX ....: 12
CREATE DATABASE .: 0 DROP DATABASE ...: 0 ALTER DATABASE ..: 0
CREATE STOGROUP .: 0 DROP STOGROUP ...: 0 ALTER STOGROUP ..: 0
CREATE TABSPACE .: 0 DROP TABSPACE ...: 0 ALTER TABSPACE ..: 0
CREATE TABLE ....: 30 DROP TABLE ......: 11614 ALTER TABLE .....: 0
CREATE AUX TABLE : 0 CREATE TMP TABLE : 0 DECLARE TMP TABLE: 11600
CREATE INDEX ....: 6 DROP INDEX ......: 0 ALTER INDEX .....: 0
CREATE VIEW .....: 12 DROP VIEW .......: 12 ALTER VIEW ......: 6
CREATE SYNONYM ..: 0 DROP SYNONYM ....: 0
CREATE ALIAS ....: 0 DROP ALIAS ......: 0
CREATE SEQUENCE .: 6 DROP SEQUENCE ...: 6 ALTER SEQUENCE ..: 6
CREATE TRIGGER ..: 6 DROP TRIGGER ....: 6
CREATE DIST TYPE : 6 DROP DIST TYPE ..: 6
CREATE FUNCTION .: 6 DROP FUNCTION ...: 6 ALTER FUNCTION ..: 6
CREATE PROCEDURE : 6 DROP PROCEDURE ..: 6 ALTER PROCEDURE .: 6
CREATE ROLE .....: 12 DROP ROLE .......: 12
CREATE TRUST CONT: 6 DROP TRUST CONT .: 6 ALTER TRUST CONT : 6
CREATE MASK/PERM : 0 DROP MASK/PERM ..: 0 ALTER MASK/PERM .: 0
CREATE VARIABLE .: 0 DROP VARIABLE....: 0
DROP PACKAGE ....: 0 ALTER JAR .......: 0
SET CUR SQL ID ..: 25 SET HOST VAR ....: 34657212 SET CONNECTION ..: 0
SET CUR DEGREE ..: 0 SET CUR RULES ...: 0 SET CUR PATH ....: 0
SET CUR PRECISION: 0 SET TIMEOUT (APP): 0 SET TIMEOUT (PRF): 0
MULTI-ROW PROCESSING:
ROWS FETCHED ....: 75968806 ROWS INSERTED ...: 2915959 ROWS UPDATED ....: 3486197
ROWS DELETED ....: 490627
- SELECT
-
The number of SQL SELECT statements executed.
Field Name: QXSELECT
- INSERT
-
The number of INSERT statements executed.
Field Name: QXINSRT
- UPDATE
-
The number of UPDATE statements executed.
Field Name: QXUPDTE
- DELETE
-
The number of DELETE statements executed.
Field Name: QXDELET
- TYPE 1 INSRT ALG
-
Inserts performed via algorithm type 1.
Background and Tuning Information
Inserts performed via algorithm type 1.
Field Name: QXRWSINSRTDALG1
- PREPARE
-
The number of SQL PREPARE statements executed. This number at the server location might not match the number at the user application because of the internal processing of the Distributed Data Facility (DDF).
Field Name: QXPREP
- DESCRIBE
-
The number of DESCRIBE, DESCRIBE CURSOR, DESCRIBE INPUT, and DESCRIBE PROCEDURE statements executed. This number at the server location might not match the number at the user application because of the internal processing of the Distributed Data Facility (DDF).
Field Name: QXDESC
- TYPE 2 INSRT ALG
-
Inserts performed via algorithm type 2.
Background and Tuning Information
Inserts performed via algorithm type 2.
Field Name: QXRWSINSRTDALG2
- OPEN
-
The number of OPEN statements executed.
Field Name: QXOPEN
- CLOSE
-
The number of CLOSE statements executed. This number at the server location might not match the number at the user application because of the internal processing of the Distributed Data Facility (DDF).
Field Name: QXCLOSE
- FETCH
-
The number of FETCH statements executed. This number at the server location might not match the number at the user application because of the internal processing of the Distributed Data Facility (DDF).
Field Name: QXFETCH
- COMMENT ON
-
The number of COMMENT ON statements executed.
Field Name: QXCMTON
- LOCK TABLE
-
The number of LOCK TABLE statements executed.
Field Name: QXLOCK
- GRANT
-
The number of GRANT statements executed.
Field Name: QXGRANT
- REVOKE
-
The number of REVOKE statements executed.
Field Name: QXREVOK
- INCREMENTAL BINDS
-
The number of incremental binds (excluding prepare). It is incremented by:
- SQL statements with BIND VALIDATE(RUN) that fail at bind time and are bound again at execution time
- Static DDL statements (such as CREATE TABLE, DROP TABLE, LOCK TABLE) that use DB2 private protocol
Background and Tuning Information
If a plan is bound with VALIDATE(RUN), DB2 performs validity checks at bind time and rechecks any failures at run time. This can result in catalog contention and degraded application performance, depending on the number of statements flagged and how many times they are executed. Avoid VALIDATE(RUN) if possible. Ensure that all objects are created and all privileges are granted before bind, and select the VALIDATE(BIND) option.
Field Name: QXINCRB
- LABEL ON
-
The number of LABEL ON statements executed.
Field Name: QXLABON
- DESCRIBE TABLE
-
The number of DESCRIBE TABLE statements executed.
Field Name: QXDSCRTB
- CONNECT TYPE 1
-
The number of CONNECT type 1 statements executed.
Field Name: QXCON1
- CONNECT TYPE 2
-
The number of CONNECT type 2 statements executed.
Field Name: QXCON2
- RELEASE
-
The number of RELEASE statements executed.
Field Name: QXREL
- ASSOCIATE LOCATOR
-
The number of SQL ASSOCIATE LOCATORS statements executed.
Field Name: QXALOCL
- ALLOCATE CURSOR
-
The number of SQL ALLOCATE CURSOR statements executed.
Field Name: QXALOCC
- RENAME TABLE
-
The number of RENAME TABLE statements executed.
Field Name: QXRNTAB
- HOLD LOCATOR
-
The number of SQL HOLD LOCATOR statements executed.
Field Name: QXHOLDL
- FREE LOCATOR
-
The number of SQL FREE LOCATOR statements executed.
Field Name: QXFREEL
- MERGE
-
The number of times a MERGE statement was executed.
Field Name: QXMERGE
- TRUNCATE TABLE
-
The number of TRUNCATE TABLE statements issued.
Field Name: QXTRTBL
- RENAME INDEX
-
The number of RENAME INDEX statements issued.
Field Name: QXRNIX
- CREATE DATABASE
-
The number of CREATE DATABASE statements executed.
Field Name: QXCRDAB
- DROP DATABASE
-
The number of DROP DATABASE statements executed.
Field Name: QXDRPDB
- ALTER DATABASE
-
The number of ALTER DATABASE statements executed.
Field Name: QXALDAB
- CREATE STOGROUP
-
The number of CREATE STOGROUP statements executed.
Field Name: QXCRSTG
- DROP STOGROUP
-
The number of DROP STOGROUP statements executed.
Field Name: QXDRPST
- ALTER STOGROUP
-
The number of ALTER STOGROUP statements executed.
Field Name: QXALTST
- CREATE TABSPACE
-
The number of CREATE TABLESPACE statements executed.
Field Name: QXCTABS
- DROP TABSPACE
-
The number of DROP TABLESPACE statements executed.
Field Name: QXDRPTS
- ALTER TABSPACE
-
The number of ALTER TABLESPACE statements executed.
Field Name: QXALTTS
- CREATE TABLE
-
The number of CREATE TABLE statements executed.
Field Name: QXCRTAB
- DROP TABLE
-
The number of DROP TABLE statements executed.
Field Name: QXDRPTA
- ALTER TABLE
-
The number of ALTER TABLE statements executed.
Field Name: QXALTTA
- CREATE AUX TABLE
-
The number of CREATE AUXILIARY TABLE statements executed.
Field Name: QXCRATB
- CREATE TMP TABLE
-
The number of CREATE GLOBAL TEMPORARY TABLE statements executed.
Field Name: QXCRGTT
- DECLARE TMP TABLE
-
The number of DECLARE GLOBAL TEMPORARY TABLE statements executed.
Field Name: QXDCLGTT
- CREATE INDEX
-
The number of CREATE INDEX statements executed.
Field Name: QXCRINX
- DROP INDEX
-
The number of DROP INDEX statements executed.
Field Name: QXDRPIX
- ALTER INDEX
-
The number of ALTER INDEX statements executed.
Field Name: QXALTIX
- CREATE VIEW
-
The number of CREATE VIEW statements executed.
Field Name: QXDEFVU
- DROP VIEW
-
The number of DROP VIEW statements executed.
Field Name: QXDRPVU
- ALTER VIEW
-
The number of ALTER VIEW statements issued.
Field Name: QXALTVW
- CREATE SYNONYM
-
The number of CREATE SYNONYM statements executed.
Field Name: QXCRSYN
- DROP SYNONYM
-
The number of DROP SYNONYM statements executed.
Field Name: QXDRPSY
- CREATE ALIAS
-
The number of CREATE ALIAS statements executed.
Field Name: QXCRALS
- DROP ALIAS
-
The number of SQL DROP ALIAS statements executed.
Field Name: QXDRPAL
- CREATE SEQUENCE
-
The number of CREATE SEQUENCE statements.
Field Name: QXCRESEQ
- DROP SEQUENCE
-
The number of ALTER SEQUENCE statements.
Field Name: QXALTSEQ
- ALTER SEQUENCE
-
The number of DROP SEQUENCE statements.
Field Name: QXDROSEQ
- CREATE TRIGGER
-
The number of SQL CREATE TRIGGER statements.
Field Name: QXCRTRIG
- DROP TRIGGER
-
The number of DROP TRIGGER statements executed.
Field Name: QXDRPTR
- CREATE DIST TYPE
-
The number of CREATE DISTINCT TYPE statements executed.
Field Name: QXCDIST
- DROP DIST TYPE
-
The number of DROP DISTINCT TYPE statements executed.
Field Name: QXDDIST
- CREATE FUNCTION
-
The number of CREATE FUNCTION statements executed.
Field Name: QXCRUDF
- DROP FUNCTION
-
The number of DROP FUNCTION statements executed.
Field Name: QXDRPFN
- ALTER FUNCTION
-
The number of DROP DISTINCT TYPE statements executed.
Field Name: QXDDIST
- CREATE PROCEDURE
-
The number of CREATE PROCEDURE statements issued.
Field Name: QXCRPRO
- DROP PROCEDURE
-
The number of DROP PROCEDURE statements executed.
Field Name: QXDRPPR
- ALTER PROCEDURE
-
The number of ALTER PROCEDURE statements executed.
Field Name: QXALPRO
- CREATE ROLE
-
The number of CREATE ROLE statements executed.
Field Name: QXCRROL
- DROP ROLE
-
The number of DROP ROLE statements issued.
Field Name: QXDRPROL
- CREATE TRUST CONT
-
The number of CREATE TRUSTED CONTEXT statements issued.
Field Name: QXCRCTX
- DROP TRUST CONT
-
The number of DROP TRUSTED CONTEXT statements issued.
Field Name: QXDRPCTX
- ALTER TRUST CONT
-
The number of ALTER TRUSTED CONTEXT statements issued.
Field Name: QXALTCTX
- CREATE MASK/PERM
-
The number of CREATE MASK and CREATE PERMISSION statements executed.
Field Name: QXCREMP
- DROP MASK/PERM
-
The number of DROP MASK and DROP PERMISSION statements executed.
Field Name: QXDRPMP
- ALTER MASK/PERM
-
The number of ALTER MASK and ALTER PERMISSION statements executed.
Field Name: QXALTMP
- CREATE VARIABLE
-
The number of CREATE VARIABLE statements.
Field Name: QXCRTSV
- DROP VARIABLE
-
The number of DROP VARIABLE statements.
Field Name: QXDRPSV
- DROP PACKAGE
-
The number of SQL DROP PACKAGE statements executed.
Field Name: QXDRPPKG
- ALTER JAR
-
The number of ALTER JAR statements issued.
Field Name: QXALTJR
- SET CUR SQL ID
-
The number of SET CURRENT SQLID statements executed.
Field Name: QXSETSQL
- SET HOST VAR
-
The number of SET HOST VARIABLE statements executed. The special register that was retrieved is not tracked.
Field Name: QXSETHV
- SET CONNECTION
-
The number of SET CONNECTION statements executed.
Field Name: QXSETCON
- SET CUR DEGREE
-
The number of SET CURRENT DEGREE statements executed.
Field Name: QXSETCDG
- SET CUR RULES
-
The number of SET CURRENT RULES statements executed.
Field Name: QXSETCRL
- SET CUR PATH
-
The number of SET CURRENT PATH statements executed.
Field Name: QXSETPTH
- SET CUR PRECISION
-
The number of SET CURRENT PRECISION statements executed.
Field Name: QXSETCPR
- SET TIMEOUT (APP)
-
Number of times the SET CURRENT LOCK TIMEOUT statement was executed from application.
Field Name: QXCTOAPP
- SET TIMEOUT (PRF)
-
Number of times that the CURRENT LOCK TIMEOUT special register was set from a profile table.
Field Name: QXCTOPRF
- ROWS FETCHED
-
The number of rows fetched.
Field Name: QXRWSFETCHD
- ROWS INSERTED
-
The number of rows inserted.
Field Name: QXRWSINSRTD
- ROWS UPDATED
-
The number of rows updated.
Field Name: QXRWSUPDTD
- ROWS DELETED
-
The number of rows deleted.
Field Name: QXRWSDELETD