Re: SQL0084 after upgrade to V7R1 on ODBC connection

A little more information now that I have used DBMON and done some more
research....

I thought it was fixed but customer reports this is still an issue.
Environment is a Windows NT box running an IVR app that uses an ODBC
interface to I/O to DB2 on i. App has been working flawlessly since
1996 (yeah, really).

It appears that the i isn't happy with this SQL statement:

UPDATE PASA408 SET ABSDSB = ? WHERE ABSID = ? AND ABSDSB = ?

The *actual* SQL statement that is submitted from the IVR is this:

update pasa408 set absdsb = 0 where absid = ? and absdsb = ?

So I am not sure how the absdsb = 0 gets translated to ABSDSB = ? (guess
that the db2 for i database does some optimization). DBMON reports
SQLSTATE 42612 with SQLCODE -84. The Hit ODBC driver returns this to
the IVR status screen:

>> SQLExecDirect fail : -1
index --> 0, hstindex --> 7
SQLState : HY000
NativeError :
?
ErrorMsg : [HiT][HiT ODBC/400][SQL/400][ODBS Error]SQL statement not
allowed.

So I am not sure where to go with this. Apparently this kind of
statement is no longer allowed (seems benign enough). The purpose of
the SQL statement is to set the value of absdsb back to zero. I have a
limited bag of tricks in this particular IVR environment. Not a lot of
SQL options but I am willing to try...

Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java

On 10/3/2012 8:47 AM, Pete Helgren wrote:

Thanks. I'll try them out. I know that I captured the error. I
stopped the process on the NT box that connects using ODBC and then
started the monitor and then started the process. Then I kept checking
the job log of the QZDASOINIT job until the error was thrown. Then I
stopped the monitor. So it is in there...somewhere.

I appreciate the help!

Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java

On 10/3/2012 7:36 AM, Vernon Hamberg wrote:

Hi Pete

The Navigator tool has queries for the database monitor - there is an
error summary and an error information report. They have an option to
modify the queries, so I've copied them here - the point is, did the
monitor catch the error - but it's a start.

The statements have vhcurlib.qzg0000122 as the table name - change that
to whatever table you used as the output - and these are in SQL naming,
so the name separator is a period, not a slash.

The information-report query is a BEARRRRR! :)

Enjoy!

Vern

/* Database Performance Monitor Error Summary */
SELECT
CASE
WHEN qqc81 <= '00000' THEN 'Success'
WHEN qqc81 < '02000' THEN 'Warning'
WHEN qqc81 < '03000' THEN 'No Data'
ELSE 'Error'
END AS "Statement Outcome",
qqc81 AS "SQLSTATE",
qqi8 AS "SQLCODE",
COUNT(*) AS "Total"

FROM vhcurlib.qzg0000122
WHERE qqrid = 1000 AND qqc21 <> 'MT'
GROUP BY qqc81, qqi8
ORDER BY "SQLSTATE", "SQLCODE" DESC;

/* Database Performance Monitor Error Information */

WITH gg AS
(SELECT qqjfld,
qqucnt,
SUM(CASE qqrid WHEN 3000 THEN 1 ELSE 0 END) AS scans,
SUM(CASE qqrid WHEN 3002 THEN 1 ELSE 0 END) AS indexcrt,
SUM(CASE WHEN qqrid IN(3000, 3001, 3002) AND qqidxa = 'Y'

THEN

1 ELSE 0 END) AS indexadv,
SUM(CASE qqrid WHEN 3004 THEN 1 ELSE 0 END) AS temps,
SUM(CASE qqrid WHEN 3003 THEN 1 ELSE 0 END) AS sorts,
SUM(CASE WHEN qqrid = 3006 AND QQC11 = 'Y' THEN 1 ELSE 0

END)

AS rebuilds,
SUM(CASE WHEN qqrid = 3006 AND QQC11 = 'N' THEN 1 ELSE 0

END)

AS defers,
SUM(CASE WHEN qqrid = 3007 AND qqc11 = 'Y' THEN 1 ELSE 0

END)

AS timeouts,
SUM(CASE WHEN qqrid = 3014 AND qqc12 = 'Y' THEN 1 ELSE 0

END)

AS governors,
SUM(CASE WHEN qqrid = 3014 AND qqc16 = 'Y' THEN 1 ELSE 0

END)

AS sqe,
MAX(CASE WHEN qqrid = 3014 THEN qqtim1 ELSE NULL END) AS
lastrebuild,
MAX(CASE WHEN qqrid IN(3000, 3001, 3002) THEN qqtotr ELSE

NULL

END) AS maxrows,
MAX(CASE WHEN qqrid IN(3000, 3001, 3002) THEN qqrest ELSE

NULL

END) AS maxestrows,
AVG(CASE WHEN qqrid IN(3000, 3001, 3002) THEN qqtotr ELSE

NULL

END) AS avgrows,
AVG(CASE WHEN qqrid IN(3000, 3001, 3002) THEN qqrest ELSE

NULL

END) AS avgestrows,
SUM((CASE WHEN qqrid = 3001 THEN 1 ELSE NULL END)) AS

indexused

FROM vhcurlib.qzg0000122 a
WHERE a.qqc21 <> 'MT' AND a.qqrid <> 1000
GROUP BY qqjfld, qqucnt ),

aa AS(
SELECT CASE a.qqucnt WHEN 0 THEN qq1000 CONCAT CAST(RRN(a) AS CHAR(10))
ELSE CAST(a.qqucnt AS CHAR(10)) END AS otherjfld,
a.*,
scans,
indexcrt,
indexadv,
temps,
sorts,
rebuilds,
defers,
timeouts,
governors,
sqe,
lastrebuild,
maxrows,
maxestrows,
avgrows,
avgestrows,
indexused,
RRN(a) AS rrna
FROM vhcurlib.qzg0000122 a LEFT JOIN gg b
ON a.qqjfld = b.qqjfld AND a.qqucnt = b.qqucnt
WHERE qqrid=1000 AND qqc21 <> 'MT' AND qqc81 > '00000' AND qqc81 <>
'02000' ) ,

xx AS(
SELECT MIN(qqstim) AS firstuse,
MAX(qqstim) AS lastuse,
SUBSTR(MIN(CHAR(qqstim) CONCAT qqc21 ),27,2) AS firststmt,
SUBSTR(MAX(CHAR(qqstim) CONCAT qqc21 ),27,2) AS laststmt,
CASE
WHEN qqucnt = 0 OR SUBSTR(MAX(qqc21),1,1) IN ('I','S') THEN
MAX(qqc21)
ELSE SUBSTR(MIN(CHAR(qqstim) CONCAT qqc21 ),27,1) CONCAT
SUBSTR(MAX(CHAR(qqstim) CONCAT qqc21 ),27,1)
END AS qqstop,
MIN(CAST(RRN(aa) AS INTEGER)) AS firstrrn,
DECIMAL(SUM(qqi6),21,0) AS tottime,
SUBSTR(MAX(CASE qqc21 WHEN 'HC' THEN '1' CONCAT qq1000
WHEN 'FE' THEN '2' CONCAT qq1000
ELSE '3' CONCAT qq1000 END),2) AS

stmt,

DECIMAL( SUM(CASE qqc21 WHEN 'OP' THEN qqi6 ELSE NULL

END),21,0)

AS opentime,
DECIMAL( SUM(CASE qqc21 WHEN 'FE' THEN qqi6 ELSE NULL

END),21,0)

AS fetchtime,
DECIMAL( SUM(CASE WHEN qqc21 IN('CL','HC') THEN qqi6 ELSE NULL
END),21,0) AS closetime,
DECIMAL( SUM(CASE WHEN qqc21 NOT IN ('OP','CL','HC','FE') THEN
qqi6 ELSE NULL END),21,0) AS othertime,
SUM(CASE WHEN (qvc1c = 'Y' OR (qqc21 IN('DL', 'UP') AND qqc181
<= ' ') OR qqc21 IN ('IN', 'IC', 'SK', 'SI') OR qqc21 LIKE 'O%') AND
qqi5 = 0 THEN 1 ELSE NULL END) AS fullopen,
SUM(CASE WHEN (qvc1c = 'Y' OR (qqc21 IN('DL', 'UP') AND qqc181
<= ' ') OR qqc21 IN ('IN', 'IC', 'SK', 'SI') OR qqc21 LIKE 'O%') AND
qqi5 > 0 THEN 1 ELSE NULL END) AS pseudoopen,
1 AS stmtcnt,
MAX(scans) AS scans,
DECIMAL(DECIMAL(MAX(indexused),18,3)/MAX(CASE WHEN
(qvc1c = 'Y' OR (qqc21 IN('DL', 'UP') AND qqc181 <= ' ') OR
qqc21 IN ('IN', 'IC', 'SK', 'SI') OR qqc21 LIKE 'O%')
AND indexused IS NOT NULL THEN 1 ELSE NULL END),18,3) AS

indexused,

MAX(indexcrt) AS indexcrt,
MAX(indexadv) AS indexadv,
MAX(temps) AS temps,
MAX(sorts) AS sorts,
MAX(rebuilds) AS rebuilds,
MAX(defers) AS defers,
MAX(timeouts) AS timeouts,
MAX(governors) AS governors,
SUM(sqe) AS sqe,
MAX(lastrebuild) AS lastrebuild,
MAX(maxrows) AS maxrows,
MAX(maxestrows) AS maxestrows,
MAX(qqi2) AS maxrowsret,
DECIMAL(AVG(avgrows),18,3) AS avgrows,
DECIMAL(AVG(avgestrows),18,3) AS avgestrows,
DECIMAL(AVG(qqi2),18,3) AS avgrowsret,
qqucnt,
qqjfld,
qqi5,
otherjfld,
qqrdbn
FROM aa
GROUP BY qqrdbn,qqjfld,qqucnt,qqi5,otherjfld ),

zz AS (
SELECT TIMESTAMP(SUBSTR(MAX(DIGITS(tottime) CONCAT CHAR(firstuse)
),22,26)) AS tmaxuse,
DECIMAL(MAX(tottime)/1000000,21,6) AS tmaxtime,
DECIMAL(AVG(tottime)/1000000,21,6) AS tavgtime,
DECIMAL(MIN(tottime)/1000000,21,6) AS tmintime,
DECIMAL(DECIMAL(SUBSTR(MAX(DIGITS(tottime) CONCAT

CHAR(opentime)

),22,10),21,0)/1000000,21,6) AS topentime,
DECIMAL(DECIMAL(SUBSTR(MAX(DIGITS(tottime) CONCAT
CHAR(fetchtime) ),22,10),21,0)/1000000,21,6) AS tfetchtime,
DECIMAL(DECIMAL(SUBSTR(MAX(DIGITS(tottime) CONCAT
CHAR(closetime) ),22,10),21,0)/1000000,21,6) AS tclosetime,
DECIMAL(DECIMAL(SUBSTR(MAX(DIGITS(tottime) CONCAT
CHAR(othertime) ),22,10),21,0)/1000000,21,6) AS tothertime,
MAX(firstuse) AS tlastuse,
SUBSTR(MAX(DIGITS(tottime) CONCAT qqstop ),22,2) AS tqqstop,
SUM(stmtcnt) AS tstmtcnt,
stmt,
SUM(fullopen) AS tfullopen,
SUM(pseudoopen) AS tpseudoopen,
SUM(scans) AS tscans,
DECIMAL(AVG(indexused),5,3) AS tindexused,
SUM(indexcrt) AS tindexcrt,
SUM(indexadv) AS tindexadv,
SUM(temps) AS ttemps,
SUM(sorts) AS tsorts,
SUM(rebuilds) AS trebuilds,
SUM(defers) AS tdefers,
SUM(timeouts) AS ttimeouts,
SUM(governors) AS tgovernors,
SUM(sqe) AS tsqe,
MAX(lastrebuild) AS tlastrebuild,
MAX(maxrows) AS tmaxrows,
MAX(maxestrows) AS tmaxestrows,
MAX(maxrowsret) AS tmaxrowsret,
DECIMAL(AVG(avgrows),18,3) AS tavgrows,
DECIMAL(AVG(avgestrows),18,3) AS tavgestrows,
DECIMAL(AVG(avgrowsret),18,3) AS tavgrowsret,
INTEGER(SUBSTR(MAX(DIGITS(tottime) CONCAT DIGITS(firstrrn)
),22,10)) AS maxexrrn,
qqrdbn
FROM xx
GROUP BY qqrdbn, stmt ),

yy AS
(SELECT max(qqc12) AS qqhvi,
max(qq1000) AS qqhvar,
qqi5 AS refresh,
qqjfld,
qqucnt
FROM vhcurlib.qzg0000122 WHERE qqrid=3010
GROUP BY qqi5, qqjfld, qqucnt )

SELECT

CASE a.qqc14
WHEN 'S' THEN 'Successful'
WHEN 'U' THEN 'Unsuccessful'
ELSE a.qqc14
END AS "Statement Outcome",

a.qqc81 AS "SQLSTATE",
a.qqi8 AS "SQLCODE",

/* Statement Identification */
CASE tqqstop
WHEN 'AL' THEN 'ALTER TABLE'
WHEN 'AQ' THEN 'ALTER SEQUENCE'
WHEN 'CA' THEN 'CALL'
WHEN 'CC' THEN CASE WHEN a.qqucnt = 0 THEN 'CREATE SCHEMA' ELSE 'CLOSE'

END

WHEN 'CD' THEN 'CREATE DISTINCT TYPE'
WHEN 'CF' THEN 'CREATE FUNCTION'
WHEN 'CG' THEN 'CREATE TRIGGER'
WHEN 'CI' THEN 'CREATE INDEX'
WHEN 'CL' THEN 'CLOSE'
WHEN 'CM' THEN 'COMMIT'
WHEN 'CN' THEN 'CONNECT'
WHEN 'CO' THEN 'COMMENT ON'
WHEN 'CP' THEN 'CREATE PROCEDURE'
WHEN 'CQ' THEN 'CREATE SEQUENCE'
WHEN 'CS' THEN 'CREATE ALIAS'
WHEN 'CT' THEN 'CREATE TABLE'
WHEN 'CV' THEN 'CREATE VIEW'
WHEN 'DC' THEN 'DECLARE CURSOR'
WHEN 'DD' THEN 'DELETE...DELETE'
WHEN 'DE' THEN 'DESCRIBE'
WHEN 'DF' THEN 'DELETE...FETCH'
WHEN 'DH' THEN 'DELETE...CLOSE (Hard)'
WHEN 'DI' THEN 'DISCONNECT'
WHEN 'DK' THEN 'DELETE...CLOSE'
WHEN 'DL' THEN 'DELETE'
WHEN 'DM' THEN 'DESCRIBE INPUT'
WHEN 'DP' THEN 'DECLARE PROCEDURE'
WHEN 'DR' THEN 'DROP'
WHEN 'DT' THEN 'DESCRIBE TABLE'
WHEN 'DU' THEN 'DELETE...UPDATE'
WHEN 'EI' THEN 'EXECUTE IMMEDIATE'
WHEN 'EX' THEN 'EXECUTE'
WHEN 'FC' THEN 'FETCH...CLOSE'
WHEN 'FD' THEN 'FETCH...DELETE'
WHEN 'FE' THEN 'FETCH'
WHEN 'FF' THEN 'FETCH...FETCH'
WHEN 'FH' THEN 'FETCH...CLOSE (Hard)'
WHEN 'FL' THEN 'FREE LOCATOR'
WHEN 'FU' THEN 'FETCH...UPDATE'
WHEN 'GD' THEN 'GET DIAGNOSTICS'
WHEN 'GR' THEN 'GRANT'
WHEN 'HC' THEN 'CLOSE (Hard)'
WHEN 'HL' THEN 'HOLD LOCATOR'
WHEN 'IC' THEN 'INSERT'
WHEN 'IN' THEN 'INSERT'
WHEN 'JR' THEN 'Recycle Prestart Job'
WHEN 'LK' THEN 'LOCK'
WHEN 'LO' THEN 'LABEL ON'
WHEN 'OC' THEN 'OPEN...CLOSE'
WHEN 'OD' THEN 'OPEN...DELETE'
WHEN 'OF' THEN 'OPEN...FETCH'
WHEN 'OH' THEN 'OPEN...CLOSE (Hard)'
WHEN 'OO' THEN 'OPEN'
WHEN 'OP' THEN 'OPEN'
WHEN 'OU' THEN 'OPEN...UPDATE'
WHEN 'PD' THEN 'PREPARE...DESCRIBE'
WHEN 'PR' THEN 'PREPARE'
WHEN 'RB' THEN 'ROLLBACK SAVEPOINT'
WHEN 'RE' THEN 'RELEASE'
WHEN 'RF' THEN 'REFRESH TABLE'
WHEN 'RG' THEN 'RESIGNAL'
WHEN 'RO' THEN 'ROLLBACK'
WHEN 'RS' THEN 'RELEASE SAVEPOINT'
WHEN 'RT' THEN 'RENAME'
WHEN 'RV' THEN 'REVOKE'
WHEN 'SA' THEN 'SAVEPOINT'
WHEN 'SC' THEN 'SET CONNECTION'
WHEN 'SE' THEN 'SET ENCRYPTION PASSWORD'
WHEN 'SG' THEN 'SIGNAL'
WHEN 'SI' THEN 'SELECT INTO'
WHEN 'SK' THEN 'SELECT INTO'
WHEN 'SP' THEN 'SET PATH'
WHEN 'SR' THEN 'SET RESULTS'
WHEN 'SS' THEN 'SET CURRENT SCHEMA'
WHEN 'ST' THEN 'SET TRANSACTION'
WHEN 'SV' THEN 'SET VARIABLE'
WHEN 'UC' THEN 'UPDATE...CLOSE'
WHEN 'UD' THEN 'UPDATE...DELETE'
WHEN 'UF' THEN 'UPDATE...FETCH'
WHEN 'UH' THEN 'UPDATE...CLOSE (Hard)'
WHEN 'UP' THEN 'UPDATE'
WHEN 'UU' THEN 'UPDATE...UPDATE'
WHEN 'VI' THEN 'VALUES INTO'
WHEN 'VV' THEN 'VALUES INTO'
ELSE tqqstop
END AS "Operation",

tstmtcnt AS "Statement Usage Count",
zz.qqrdbn AS "Relational Database Name",
stmt AS "Statement Text",
varchar(qqhvar,500) AS "Host Variable Values",

/* Time */
tmaxuse AS "Most Expensive Use",

/* Costs */
tmaxtime AS "Maximum Runtime",
tavgtime AS "Average Runtime",
tmintime AS "Minimum Runtime",
topentime AS "Maximum Open Time",
tfetchtime AS "Maximum Fetch Time",
tclosetime AS "Maximum Close Time",
tothertime AS "Maximum Other Time",
tlastuse AS "Last Use",

/* Opens */
tfullopen AS "Full Opens",
tpseudoopen AS "Pseudo Opens",

/* Row Sizes */
tmaxrows AS "Maximum Table Rows",
tmaxestrows AS "Maximum Estimated Rows",
tmaxrowsret AS "Maximum Rows Returned",
tavgrows AS "Average Table Rows",
tavgestrows AS "Average Estimated Rows",
tavgrowsret AS "Average Rows Returned",

/* Implementation */
CASE qvc14
WHEN 'R' THEN 'Reusable'
WHEN 'N' THEN 'Non-Reusable'
ELSE qvc14
END AS "ODP Implementation",

CASE
WHEN tsqe > 0 AND tlastrebuild IS NOT NULL THEN 'SQE'
WHEN tsqe = 0 AND tlastrebuild IS NOT NULL THEN 'CQE'
ELSE NULL
END AS "Optimizer",

CASE qqc13
WHEN 'Y' THEN 'Yes'
WHEN 'N' THEN 'No'
ELSE qqc13
END AS "Parse Required",

CASE qqhvi
WHEN 'I' THEN 'ISV'
WHEN 'V' THEN 'V2'
WHEN 'U' THEN 'UP'
WHEN 'S' THEN 'SQL Insert or Update'
ELSE qqhvi
END AS "Host Variable Implementation",

CASE qqc16
WHEN 'N' THEN NULL
WHEN '0' THEN NULL
WHEN '1' THEN 'Different Lengths'
WHEN '2' THEN 'Different Numeric Types'
WHEN '3' THEN 'C NUL-terminated Variable'
WHEN '4' THEN 'Varying Length Fixed Length'
WHEN '5' THEN 'CCSID Conversion'
WHEN '6' THEN 'DRDA Mapping Required'
WHEN '7' THEN 'Datetime Column'
WHEN '8' THEN 'Too Many Host Variables'
WHEN '9' THEN 'Target Table Is Not An SQL Table'
ELSE qqc16
END AS "Data Conversion",

/* Implementation */
tscans AS "Total Table Scans",

/* Index Information */
tindexused AS "Average Indexes Used",
tindexcrt AS "Total Indexes Created",
tindexadv AS "Total Index Creates Advised",

/* Copy of Data */
ttemps AS "Total Temporary Tables",
tsorts AS "Total Sorts",

/* Optimizer Information */
ttimeouts AS "Total Optimizer Time Outs",
tgovernors AS "Total Governor Time Outs",

/* Access Plan Rebuild */
tlastrebuild AS "Last Access Plan Rebuilt",
trebuilds AS "Total Access Plans Rebuilt",
tdefers AS "Total Rebuilds Deferred",
CASE qqc22
WHEN 'A1' THEN 'Different File Or Member'
WHEN 'A2' THEN 'Reusable Plan to Non-Reusable Plan Change'
WHEN 'A3' THEN 'Non-Reusable To Reusable Plan Change'
WHEN 'A4' THEN 'More Than Ten Percent Change In Number Of Rows'
WHEN 'A5' THEN 'New Access Path Found'
WHEN 'A6' THEN 'Access Path No Longer Found Or Valid'
WHEN 'A7' THEN 'System Programming Change'
WHEN 'A8' THEN 'Different CCSID'
WHEN 'A9' THEN 'Different Date Or Time Format'
WHEN 'AA' THEN 'Different Sort Sequence Table'
WHEN 'AB' THEN 'Different Storage Pool or Paging Option'
WHEN 'AC' THEN 'Symmetric MultiProcessing Change'
WHEN 'AD' THEN 'Different DEGREE'
WHEN 'AE' THEN 'Open View or View Materialization'
WHEN 'AF' THEN 'UDF or UDT Change'
WHEN 'B0' THEN 'QAQQINI Change'
WHEN 'B1' THEN 'Different Isolation Level or Scroll Option'
WHEN 'B2' THEN 'Different FETCH FIRST n ROWS'
WHEN 'B3' THEN 'First Run With Variable Values'
WHEN 'B4' THEN 'Different Constraints'
WHEN 'NR' THEN 'New Release'
ELSE qqc22
END AS "Access Plan Rebuild Reason",
HEX(qvc21) AS "Rebuild Subcode",

/* SQL Attributes */
CASE qvc41
WHEN 'NC' THEN 'No Commit'
WHEN 'UR' THEN 'Uncommitted Read'
WHEN 'CS' THEN 'Cursor Stability'
WHEN 'CSKL' THEN 'Cursor Stability KEEP LOCKS'
WHEN 'RS' THEN 'Read Stability'
WHEN 'RR' THEN 'Repeatable Read'
ELSE qvc41
END AS "Isolation Level",
qqia AS "First N Rows Only",
qqf1 AS "Optimize For N Rows",
CASE qqc61
WHEN 'NSA' THEN 'Non-scrollable Asensitive'
WHEN 'NSI' THEN 'Non-scrollable Insensitive'
WHEN 'NSS' THEN 'Non-scrollable Sensitive'
WHEN 'SCA' THEN 'Scrollable Asensitive'
WHEN 'SCI' THEN 'Scrollable Insensitive'
WHEN 'SCS' THEN 'Scrollable Sensitive'
ELSE qqc61
END AS "Cursor Type",
CASE qvc11
WHEN 'Y' THEN 'Yes'
WHEN 'N' THEN 'No'
WHEN 'O' THEN 'Optimize'
ELSE qvc11
END AS "Allow Copy Data",
CASE qvc15
WHEN 'S' THEN '*READ'
WHEN 'L' THEN '*ALLREAD'
WHEN 'F' THEN '*NONE'
ELSE qvc15
END AS "Blocking Enabled",
CASE qqc18
WHEN 'C' THEN 'Column-wise'
WHEN 'R' THEN 'Row-wise'
ELSE qqc18
END AS "Binding Type",
CASE qvc16
WHEN 'Y' THEN 'Yes'
WHEN 'N' THEN 'No'
ELSE qvc16
END AS "Delay Prepare",
CASE qvc12
WHEN 'Y' THEN 'Yes'
WHEN 'N' THEN 'No'
ELSE qvc12
END AS "Pseudo Open",
CASE qvc13
WHEN 'Y' THEN 'Yes'
WHEN 'N' THEN 'No'
ELSE qvc13
END AS "Pseudo Close",
CASE qvc17
WHEN 'N' THEN 'System'
WHEN 'S' THEN 'SQL'
ELSE qvc17
END AS "Naming",
qvc1a AS "User Profile",
qvc1b AS "Dynamic User Profile",
qvc1281 AS "Default Collection",
qvc1000 AS "SQL Path",
qvc42 AS "Date Format",
qwc11 AS "Date Separator",
qvc43 AS "Time Format",
qwc12 AS "Time Separator",
qwc13 AS "Decimal Point",
qvp156 AS "Maximum Precision",
qvp157 AS "Maximum Scale",
qvp158 AS "Minimum Divide Scale",
qvc105 AS "NLSS Table Library",
qvc104 AS "NLSS Table",
qvc44 AS "Language ID",
qvc23 AS "Country or Region ID",
CASE qwc19
WHEN 'Y' THEN 'Yes'
WHEN 'N' THEN 'No'
ELSE qwc19
END AS "Unicode Data Normalization",
qvc101 AS "Close Cursor",
CASE qvc19
WHEN 'Y' THEN 'Yes'
WHEN 'N' THEN 'No'
ELSE qvc19
END AS "Optimize LOBs",
qvp159 AS "LOB Locator Threshold",

/* Job-user-program identification */
qqjob AS "Job",
qquser AS "Job User",
qqjnum AS "Job Number",
qvc102 AS "User Name",
qqi9 AS "Thread ID",
qqc104 AS "Program Library",
qqc103 AS "Program",

/* Statement attributes */
qqudef AS "User Defined Field",
qqc181 AS "Cursor",
qqc182 AS "Statement Name",
qqstn AS "Statement Number",
CASE qqc12
WHEN 'D' THEN
CASE qvc18
WHEN 'E' THEN 'Extended Dynamic'
WHEN 'S' THEN 'System-wide Cache Dynamic'
WHEN 'L' THEN 'Dynamic'
ELSE qvc18 END
WHEN 'S' THEN 'Static'
ELSE qqc12
END AS "Statement Type",
qqi5 AS "Refresh Count",
a.qqucnt AS "Unique Count",
a.qqjfld AS "Join Field",
b.qqucnt,
b.refresh,
b.qqjfld

FROM zz
LEFT JOIN aa a
ON zz.tmaxuse=a.qqstim AND zz.maxexrrn=rrna
LEFT JOIN yy b
ON a.qqjfld=b.qqjfld AND a.qqucnt=b.qqucnt AND a.qqi5 = b.refresh

ORDER BY "SQLSTATE", "SQLCODE" DESC;

On 10/2/2012 3:32 PM, Pete Helgren wrote:

Vern,

OK. Ready for more abuse. How do I tell which of the statements that
DBMON picked up is the guilty party? There are a bunch some of which I
assumed worked (which is dangerous....) Is there a code I look for in
one of the *many* fields?


Pete Helgren
Value Added Software, Inc
www.petesworkshop.com
GIAC Secure Software Programmer-Java

On 10/2/2012 8:48 AM, Vernon Hamberg wrote:

Hi Pete

In 5250 you can run the STRDBMON command. You can set a generic job

name

there, and you want the *DETAIL records - the output is a PF, which

is a

bear to interpret by eye, but once you get that, c'mon back for more

abuse!

Vern

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.