V9 for z/OS: I Think I Broke the Parser?

Philip Sevetson

V9 for z/OS: I Think I Broke the Parser?
Anybody know what I'm doing wrong, here? I'm assuming it's got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Raymond Bell

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Philip Sevetson)
Not near a 'live' system but does the semi-colon just after the CONCAT need to be in double-quotes? i.e. CONCAT ";"

Just a thought.

Cheers,


Raymond

________________________________
From: IDUG DB2-L [[login to unmask email] On Behalf Of Sevetson, Phil [[login to unmask email]
Sent: 20 December 2010 22:39
To: [login to unmask email]
Subject: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Anybody know what I’m doing wrong, here? I’m assuming it’s got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Sevetson

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Raymond Bell)
If you put something in double quotes, it becomes a column name. I want this as part of the output string.

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Monday, December 20, 2010 5:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Not near a 'live' system but does the semi-colon just after the CONCAT need to be in double-quotes? i.e. CONCAT ";"

Just a thought.

Cheers,


Raymond

________________________________
From: IDUG DB2-L [[login to unmask email] On Behalf Of Sevetson, Phil [[login to unmask email]
Sent: 20 December 2010 22:39
To: [login to unmask email]
Subject: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Anybody know what I'm doing wrong, here? I'm assuming it's got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Bayard Tysor

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Philip Sevetson)
Hi Phil,

Try moving the " AS RESULT_LIMKEY" after the " CONCAT ';' ".

Tink

On Mon, Dec 20, 2010 at 5:39 PM, Sevetson, Phil <[login to unmask email]>wrote:

> Anybody know what I’m doing wrong, here? I’m assuming it’s got something
> to do with putting a subquery in the ELSE clause:
>
>
>
> SELECT 'ALTER TABLE '
>
> CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
>
> CONCAT '.'
>
> CONCAT TB.NAME < http://tb.name/ > ),1,19
>
>
> )
>
> , 'ALTER PART '
>
> CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
>
> , CASE
>
> WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
>
> ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
>
> (SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
>
> WHERE TB.TSNAME = TS.NAME < http://ts.name/ >
>
>
> AND TB.DBNAME = TS.DBNAME) MONTHS
>
> ) CONCAT ')'
>
> END AS RESULT_LIMKEY
>
> CONCAT ';'
>
> FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
>
> WHERE
>
> TB.NAME < http://tb.name/ > =
> 'TECHKDEDD00'
>
> AND TB.CREATOR = 'DB2FDB'
>
> AND TP.TSNAME = TB.TSNAME
>
> AND TP.DBNAME = TB.DBNAME
>
> ORDER BY TP.LOGICAL_PART DESC
>
> ;
>
>
> ---------+---------+---------+---------+---------+---------+---------+-------
>
> DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN ,
> FROM
>
> INTO WAS
> EXPECTED
>
> DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
> CODE
>
> DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING
> ERROR
>
> DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC
> INFORMATION
>
> DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000'
> X'FFFFFFFF'
>
> X'0000043B' X'000001FA' SQL DIAGNOSTIC
> INFORMATION
>
>
> ---------+---------+---------+---------+---------+---------+---------+-------
>
>
>
>
>
>
>
> ------------------------------
> >>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data:
> " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-




--
B.L. "Tink" Tysor
Bayard Lee Tysor, Inc.
[login to unmask email]
401-965-2688

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Tony Andrews

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Bayard Tysor)
You need to add a comma or CONCAT after ‘ALTER TABLE’. I believe the error is

when it hits the first CONCAT.



---------------------------------------------------------------------------------

Tony Andrews | Trainer | Application Tuning Consultant

123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | [login to unmask email] <mailto:[login to unmask email]>
For more information about Themis, visit www.themisinc.com






From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of B. L. "Tink" Tysor
Sent: Monday, December 20, 2010 5:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] V9 for z/OS: I Think I Broke the Parser?



Hi Phil,



Try moving the " AS RESULT_LIMKEY" after the " CONCAT ';' ".



Tink

On Mon, Dec 20, 2010 at 5:39 PM, Sevetson, Phil <[login to unmask email]> wrote:

Anybody know what I’m doing wrong, here? I’m assuming it’s got something to do with putting a subquery in the ELSE clause:



SELECT 'ALTER TABLE '

CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)

CONCAT '.'

CONCAT TB.NAME < http://tb.name/ > ),1,19

)

, 'ALTER PART '

CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)

, CASE

WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'

ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -

(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS

WHERE TB.TSNAME = TS.NAME < http://ts.name/ >

AND TB.DBNAME = TS.DBNAME) MONTHS

) CONCAT ')'

END AS RESULT_LIMKEY

CONCAT ';'

FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP

WHERE

TB.NAME < http://tb.name/ > = 'TECHKDEDD00'

AND TB.CREATOR = 'DB2FDB'

AND TP.TSNAME = TB.TSNAME

AND TP.DBNAME = TB.DBNAME

ORDER BY TP.LOGICAL_PART DESC

;

---------+---------+---------+---------+---------+---------+---------+-------

DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM

INTO WAS EXPECTED

DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'

X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION

---------+---------+---------+---------+---------+---------+---------+-------









________________________________

>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-




--
B.L. "Tink" Tysor
Bayard Lee Tysor, Inc.
[login to unmask email]
401-965-2688



________________________________

>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Larry Jardine

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Tony Andrews)
I think you have mixed up the syntax.

For CONCAT, it is: Concat(first,second)
which is different from using the concatenation symbol ||.

Example:

SELECT
CONCAT('FIRST','SECOND')
, 'FIRST' || 'SECOND'
FROM SYSIBM.SYSDUMMY1
WITH UR;




Larry Jardine
Database Administrator

If you have more than one standard, then you don't have a standard.



________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Monday, December 20, 2010 5:39 PM
To: [login to unmask email]
Subject: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Anybody know what I'm doing wrong, here? I'm assuming it's got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-


This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Larry Jardine

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Larry Jardine)
Or not. Maybe it should work as you have it.


Larry Jardine
Database Administrator

If you have more than one standard, then you don't have a standard.



________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Monday, December 20, 2010 5:39 PM
To: [login to unmask email]
Subject: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Anybody know what I'm doing wrong, here? I'm assuming it's got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-


This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Sevetson

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Larry Jardine)
Tink, You get the prize - I hadn't thought of that. Had some other problems but I've worked them out.

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of B. L. "Tink" Tysor
Sent: Monday, December 20, 2010 5:57 PM
To: [login to unmask email]
Subject: Re: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Hi Phil,

Try moving the " AS RESULT_LIMKEY" after the " CONCAT ';' ".

Tink
On Mon, Dec 20, 2010 at 5:39 PM, Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Anybody know what I'm doing wrong, here? I'm assuming it's got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME < http://tb.name/ > ),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME < http://ts.name/ >
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME < http://tb.name/ > = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-



--
B.L. "Tink" Tysor
Bayard Lee Tysor, Inc.
[login to unmask email]
401-965-2688

________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Sevetson

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Philip Sevetson)
Larry,
Concat works both ways, I think. I _know_ from experience that it works in place of the || characters.

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jardine, Lawrence J
Sent: Monday, December 20, 2010 6:24 PM
To: [login to unmask email]
Subject: Re: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Or not. Maybe it should work as you have it.


Larry Jardine
Database Administrator

If you have more than one standard, then you don't have a standard.


________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Monday, December 20, 2010 5:39 PM
To: [login to unmask email]
Subject: [DB2-L] V9 for z/OS: I Think I Broke the Parser?
Anybody know what I'm doing wrong, here? I'm assuming it's got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-
This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna
________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

James Campbell

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Philip Sevetson)
First LIMIT_KEY is LIMITKEY with no '_' in the name. Second LIMITKEY is not a date nor does it resemble a date field so the operation DATE(LIMITKEY) won't work.

Remove the DATE(LIMIT_KEY) and replace it with LIMITKEY and your query will work.

IE:
your edit profile using the command RECOVERY ON.
SELECT 'ALTER TABLE ' || SUBSTR(CHAR(RTRIM(TB.CREATOR) || '.' ||
TB.NAME),1,19),
'ALTER PART ' || SUBSTR(CHAR(TP.PARTITION),1,3),
CASE
WHEN LIMITKEY = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' || CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS) || ')'
END AS LIMITKEY
FROM SYSIBM.SYSTABLES TB,
SYSIBM.SYSTABLEPART TP
WHERE TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC;

Jim Campbell
Sr. Database Administrator
360-704-4015
[login to unmask email]<mailto:[login to unmask email]>

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Monday, December 20, 2010 2:39 PM
To: [login to unmask email]
Subject: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Anybody know what I'm doing wrong, here? I'm assuming it's got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Sevetson

V9 for z/OS: I Think I Broke the Parser?
(in response to James Campbell)
Okay, all, I've got a version that works, going to fine-tune it a bit.

The task is to generate a series of ALTER TABLE ALTER PARTITION statements which are correctly sequenced (alter the highest limit key first, then in descending order) for a date-partitioned, table-partitioned table. We normally, for production, use ALTER TABLE ROTATE FIRST TO LAST to keep our tables current, along with an ALTER TABLE to move the MAXVALUE from the old to the new maximum logical partition. However, this doesn't work so well with our test tables, as we don't populate them on a regular basis; in fact, some go a year between refreshes.

We have to either rotate them on a regular basis in order to be ready for the crossload of production-age data, or we have to have something like this to bring them up to date. If we don't, then at some point we will load production-age data into a table whose second-highest logical partition has a limitkey [date] that's a year or two obsolete, resulting in the highest logical partition getting a year of data. That overflows the partition limits with our larger tables. So, we need the SQL to generate this (we have probably a hundred date-partitioned tables, mostly with last-day-of-the-month limit keys).

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jardine, Lawrence J
Sent: Monday, December 20, 2010 6:24 PM
To: [login to unmask email]
Subject: Re: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Or not. Maybe it should work as you have it.


Larry Jardine
Database Administrator

If you have more than one standard, then you don't have a standard.


________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Monday, December 20, 2010 5:39 PM
To: [login to unmask email]
Subject: [DB2-L] V9 for z/OS: I Think I Broke the Parser?
Anybody know what I'm doing wrong, here? I'm assuming it's got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-
This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna
________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Sevetson

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Philip Sevetson)
Special case, Jim. My limitkeys are single column, date values only. I caught the LIMIT_KEY/LIMITKEY thing a few minutes ago, you're right. Imagine that - DB2 didn't like LIMIT_KEY.

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Campbell, Jim
Sent: Monday, December 20, 2010 6:46 PM
To: [login to unmask email]
Subject: Re: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

First LIMIT_KEY is LIMITKEY with no '_' in the name. Second LIMITKEY is not a date nor does it resemble a date field so the operation DATE(LIMITKEY) won't work.

Remove the DATE(LIMIT_KEY) and replace it with LIMITKEY and your query will work.

IE:
your edit profile using the command RECOVERY ON.
SELECT 'ALTER TABLE ' || SUBSTR(CHAR(RTRIM(TB.CREATOR) || '.' ||
TB.NAME),1,19),
'ALTER PART ' || SUBSTR(CHAR(TP.PARTITION),1,3),
CASE
WHEN LIMITKEY = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' || CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS) || ')'
END AS LIMITKEY
FROM SYSIBM.SYSTABLES TB,
SYSIBM.SYSTABLEPART TP
WHERE TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC;

Jim Campbell
Sr. Database Administrator
360-704-4015
[login to unmask email]<mailto:[login to unmask email]>

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Monday, December 20, 2010 2:39 PM
To: [login to unmask email]
Subject: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Anybody know what I'm doing wrong, here? I'm assuming it's got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-

________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Philip Sevetson

Re: V9 for z/OS: I Think I Broke the Parser?
(in response to Philip Sevetson)
And, here's the final product, which produces a single line of output on SPUFI or whoever. If your table names are short enough, it'll fit into 80 characters.

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,4)
, CASE
WHEN TP.LIMITKEY = 'MAXVALUE' THEN 'ENDING AT (MAXVALUE)'
ELSE 'ENDING AT (''' ||
CHAR(LAST_DAY(CURRENT DATE - 1 MONTH) -
((SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) - LOGICAL_PART) MONTHS
)
|| ''')'
END AS RESULT_LIMKEY
,';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = '[tablename]'
AND TB.CREATOR = '[creator]'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;

Requirements:
* A table-partitioned table which has a single date-valued column (only) as its limit key
* Partitioning is on [some date column] ASC order
* [tablename] and [creator] are supplied by you
* Note that I'm truncating the table CREATOR.NAME at 19 characters; that works for my site.
________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Monday, December 20, 2010 6:52 PM
To: [login to unmask email]
Subject: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Okay, all, I've got a version that works, going to fine-tune it a bit.

The task is to generate a series of ALTER TABLE ALTER PARTITION statements which are correctly sequenced (alter the highest limit key first, then in descending order) for a date-partitioned, table-partitioned table. We normally, for production, use ALTER TABLE ROTATE FIRST TO LAST to keep our tables current, along with an ALTER TABLE to move the MAXVALUE from the old to the new maximum logical partition. However, this doesn't work so well with our test tables, as we don't populate them on a regular basis; in fact, some go a year between refreshes.

We have to either rotate them on a regular basis in order to be ready for the crossload of production-age data, or we have to have something like this to bring them up to date. If we don't, then at some point we will load production-age data into a table whose second-highest logical partition has a limitkey [date] that's a year or two obsolete, resulting in the highest logical partition getting a year of data. That overflows the partition limits with our larger tables. So, we need the SQL to generate this (we have probably a hundred date-partitioned tables, mostly with last-day-of-the-month limit keys).

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jardine, Lawrence J
Sent: Monday, December 20, 2010 6:24 PM
To: [login to unmask email]
Subject: Re: [DB2-L] V9 for z/OS: I Think I Broke the Parser?

Or not. Maybe it should work as you have it.


Larry Jardine
Database Administrator

If you have more than one standard, then you don't have a standard.


________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Monday, December 20, 2010 5:39 PM
To: [login to unmask email]
Subject: [DB2-L] V9 for z/OS: I Think I Broke the Parser?
Anybody know what I'm doing wrong, here? I'm assuming it's got something to do with putting a subquery in the ELSE clause:

SELECT 'ALTER TABLE '
CONCAT SUBSTR(CHAR(RTRIM(TB.CREATOR)
CONCAT '.'
CONCAT TB.NAME),1,19
)
, 'ALTER PART '
CONCAT SUBSTR(CHAR(TP.PARTITION),1,3)
, CASE
WHEN DATE(LIMIT_KEY) = 'MAXVALUE' THEN 'MAXVALUE'
ELSE 'ENDING AT (' CONCAT CHAR(CURRENT DATE -
(SELECT MAX(PARTITIONS) FROM SYSIBM.SYSTABLESPACE TS
WHERE TB.TSNAME = TS.NAME
AND TB.DBNAME = TS.DBNAME) MONTHS
) CONCAT ')'
END AS RESULT_LIMKEY
CONCAT ';'
FROM SYSIBM.SYSTABLES TB, SYSIBM.SYSTABLEPART TP
WHERE
TB.NAME = 'TECHKDEDD00'
AND TB.CREATOR = 'DB2FDB'
AND TP.TSNAME = TB.TSNAME
AND TP.DBNAME = TB.DBNAME
ORDER BY TP.LOGICAL_PART DESC
;
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CONCAT. TOKEN , FROM
INTO WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 2 0 0 -1 1083 506 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000002' X'00000000' X'00000000' X'FFFFFFFF'
X'0000043B' X'000001FA' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+-------




________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-
This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna
________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-
________________________________
>>> Error in line 8 of site.mailtpl: incorrect operand type, leftover data: " = 3" <<< -> .BB &DAYSEQ(3) = 2 or &DAYSEQ(3) = 3 <-

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv