SELECT
 CASE WHEN METHOD = 0 THEN ' Initial table'
 WHEN METHOD = 1 THEN ' Nested loop '
 WHEN METHOD = 2 THEN '*Merge scan'
 WHEN METHOD = 3 THEN '*Sorts'
 WHEN METHOD = 4 THEN '*Hybrid'
 END METHOD
 ,TNAME TABLE
 ,
 CASE WHEN ACCESSTYPE = 'I' AND MATCHCOLS =0 THEN '*Index scan'
 WHEN ACCESSTYPE = 'I' AND MATCHCOLS >0 THEN ' Index access'
 WHEN ACCESSTYPE = 'I1'                 THEN ' Index 1 fetch'
 WHEN ACCESSTYPE = 'R'                  THEN '*Tablespace scan'
 WHEN ACCESSTYPE = 'N'                  THEN ' In-list index'
 WHEN ACCESSTYPE = 'M'                  THEN '*Mult IX scan next'
 WHEN ACCESSTYPE = 'MX'                 THEN '*Mult IX/read IX'
 WHEN ACCESSTYPE = 'MI'                 THEN '*Mult IX int.'
 WHEN ACCESSTYPE = 'MU'                 THEN '*Mult IX union'
 else accesstype
 END
 ,SUBSTR(ACCESSNAME,1,6) VIA
 ,CASE WHEN MATCHCOLS = 0 and method <> 3 THEN '*' ELSE ' ' END||
 SUBSTR(DIGITS(MATCHCOLS),4,2) MCOLS
 ,case when indexonly = 'Y' then 'Index only' else 'index & data' end
 ,CASE WHEN PREFETCH = 'L' THEN '*List prefetch'
 WHEN PREFETCH = 'S' THEN '*Seq prefetch'
 ELSE ' '
 END
 , CASE WHEN Join_Type = ' ' THEN 'Inner join'
 WHEN JOIN_TYPE = 'L' THEN 'Left or right outer join'
 WHEN JOIN_TYPE = 'S' THEN 'Star join'
 WHEN JOIN_TYPE = 'O' THEN 'Full outer join'
 end
 ,CASE WHEN SORTN_UNIQ = 'Y'
 OR SORTN_JOIN = 'Y'
 OR SORTN_ORDERBY = 'Y'
 OR SORTN_GROUPBY = 'Y'
 THEN
 'With New table sorts'
 ELSE '  '
 END
 ,CASE WHEN SORTC_UNIQ = 'Y'
 OR SORTC_JOIN = 'Y'
 OR SORTC_ORDERBY = 'Y'
 OR SORTC_GROUPBY = 'Y'
 THEN
 'With Composite table sorts'
 else ' '
 END
 FROM $FOCM.      .PLAN_TABLE PT1
 WHERE ---PROGNAME = 'progname'  uncomment these bits for one program
 ---AND
 BIND_TIME  = (
 SELECT MAX(BIND_TIME) FROM
 $owner.     PLAN_TABLE PT2 WHERE PT2.PROGNAME = PT1.PROGNAME
 )

 ORDER BY  QUERYNO, QBLOCKNO, PLANNO WITH UR;
  
 SELECT
 CASE WHEN METHOD = 0 THEN ' Initial table'
 WHEN METHOD = 1 THEN ' Nested loop '
 WHEN METHOD = 2 THEN '*Merge scan'
 WHEN METHOD = 3 THEN '*Sorts'
 WHEN METHOD = 4 THEN '*Hybrid'
 END METHOD
 ,TNAME TABLE
 ,
 CASE WHEN ACCESSTYPE = 'I' AND MATCHCOLS =0 THEN '*Index scan'
 WHEN ACCESSTYPE = 'I' AND MATCHCOLS >0 THEN ' Index access'
 WHEN ACCESSTYPE = 'I1'                 THEN ' Index 1 fetch'
 WHEN ACCESSTYPE = 'R'                  THEN '*Tablespace scan'
 WHEN ACCESSTYPE = 'N'                  THEN ' In-list index'
 WHEN ACCESSTYPE = 'M'                  THEN '*Mult IX scan next'
 WHEN ACCESSTYPE = 'MX'                 THEN '*Mult IX/read IX'
 WHEN ACCESSTYPE = 'MI'                 THEN '*Mult IX int.'
 WHEN ACCESSTYPE = 'MU'                 THEN '*Mult IX union'
 else accesstype
 END
 ,SUBSTR(ACCESSNAME,1,6) VIA
 ,CASE WHEN MATCHCOLS = 0 and method <> 3 THEN '*' ELSE ' ' END||
 SUBSTR(DIGITS(MATCHCOLS),4,2) MCOLS
 ,case when indexonly = 'Y' then 'Index only' else 'index & data' end
 ,CASE WHEN PREFETCH = 'L' THEN '*List prefetch'
 WHEN PREFETCH = 'S' THEN '*Seq prefetch'
 ELSE ' '
 END
 , CASE WHEN Join_Type = ' ' THEN 'Inner join'
 WHEN JOIN_TYPE = 'L' THEN 'Left or right outer join'
 WHEN JOIN_TYPE = 'S' THEN 'Star join'
 WHEN JOIN_TYPE = 'O' THEN 'Full outer join'
 end
 ,CASE WHEN SORTN_UNIQ = 'Y'
 OR SORTN_JOIN = 'Y'
 OR SORTN_ORDERBY = 'Y'
 OR SORTN_GROUPBY = 'Y'
 THEN
 'With New table sorts'
 ELSE '  '
 END
 ,CASE WHEN SORTC_UNIQ = 'Y'
 OR SORTC_JOIN = 'Y'
 OR SORTC_ORDERBY = 'Y'
 OR SORTC_GROUPBY = 'Y'
 THEN
 'With Composite table sorts'
 else ' '
 END
 FROM $FOCM.      .PLAN_TABLE PT1
 WHERE ---PROGNAME = 'progname'  uncomment these bits for one program
 ---AND
 BIND_TIME  = (
 SELECT MAX(BIND_TIME) FROM
 $owner.     PLAN_TABLE PT2 WHERE PT2.PROGNAME = PT1.PROGNAME
 )

 ORDER BY  QUERYNO, QBLOCKNO, PLANNO WITH UR;

Canberra DB2 User Group

SPUFI Hint

We all been in this position. There you are working on that great bit of SQL, you made a few changes, commented bits out added a few things and all of a sudden you get this...

---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 3548 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'00000DDC' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+

Great, so you got a dot in the wrong place, but WHICH ONE!!!!!!! Well. actually SPUFI is telling you, but you need to know how to look at the message. Look at the line prefixed by DSNT416I. The fifth number after the equals is 3548. "So?" I hear you ask. Well that is where the character is that DB2 has a problem with.

Of course it isn't quite that simple. Firstly SPUFI only recognises 72 charactes per line and lines which are entirely comments are also ignored. So what we need to do is divide this number by 72 and add 1 to get the line we are on (remembering to ignore commented lines) and use the remainder as a poistion on that line.

So now we get (3548/72) which is 49 remainder 20. Add one to the line and we get line 50 position 20. If you look at the SQL again now and go to the fiftyeth non-commented out line and look at position 20 and you will see an extra dot.

Here endeth the lesson.

 

About Us | Site Map | Privacy Policy | Contact Us | ©2007 Stevets Pty