Finding GDGs (in a column of DSNAMEs) -- blended solution

Philip Sevetson

Finding GDGs (in a column of DSNAMEs) -- blended solution
Lock, Sam,

Of course, after I published a reply, a better solution popped into my head:
CASE
WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
AND POSSTR(CANDIDATE_LEVEL,'V') = 7
AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)
,'9999999999'
,'0123456789'
)
CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)
,'9999999999'
,'0123456789'
)
='999999'
THEN 'IS GDG'
ELSE 'NOT GDG'
END AS GDG_FLAG

This mixes your stuff and mine and results in a precise solution with fewer function calls than my original. Thanks for your contributions :-)

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 12:05 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)

Lock, Sam,

I think I see a problem with that - call me a perfectionist. Also, a nit. The order of the _from-string_ and the _to-string_ needs to be reversed, according to the syntax diagram (P. 621, ch.4, "Built-in functions", topic TRANSLATE, in dsnsqm0d.pdf, the V10 SQL Reference)

1) If the period, G, and V are indiscriminately translated and some rat has put them elsewhere than they belong, this code won't find that error.

2) However, I can probably fix this with SUBSTR function to spike out the positions for ".G" and "V" --

So, this would look like:
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')
='999999999'

Which isn't as much of a savings as I'd hoped for, but is shorter and looks like it's using fewer operations (TRANSLATE and SUBSTR five times each, instead of two POSSTR, six SUBSTR, and six BETWEEN).

But even this is flawed for one case: if there's already a '9' in position 7, that's wrong for GDG levels and this code doesn't find it. (We know that there will not be a '9' in positions 1 or 2, unless the CANDIDATE_LEVEL isn't about a real GDG because the low level qualifier is too short; if so, the test fails when a period is found in some later byte of the column.)

--Phil


From: Lock Lyon [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:56 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)

Phil,

Use TRANSLATE to convert chars 0-9, 'G', and 'V' to '9', then test the string for nines, like (*NotTested* !!):

AND TRANSLATE (SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789', '999999999999') = '99999999'

- Lock Lyon

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:38 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Finding GDGs (in a column of DSNAMEs)

I have z/OS filesystem DSNAMEs in a DB2 column. Attempting to identify GDG levels in them, I'm using this:

WITH
STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)
AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),
RIGHT(RTRIM(DSNAME),9)
FROM XXXXXX.TDSNAMES_WORKING
)
SELECT
CASE
WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
AND POSSTR(CANDIDATE_LEVEL,'V') = 7
AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'
THEN 'IS GDG LEVEL'
ELSE 'NOT GDG LEVEL'
END AS GDG_FLAG
,CANDIDATE_BASE
,CANDIDATE_LEVEL
FROM STAGE1;

The CASE statement works, but I'm wondering if there's an easier way to identify a particular string as numeric in SQL. Right now, I'm testing each byte to see whether it's between 0 and 9. This works, but seems like a kind of "the hard way" to do this.

Does anyone know of a better/faster/less-SQL way to do this?

--Phil Sevetson
x1688 (212-857-1688)
3rd Floor, Cube 223
FISA HRP Database Services


-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

Lizette Koehler

Finding GDGs (in a column of DSNAMEs) -- blended solution
(in response to Philip Sevetson)
So, I am not great with SQL, but I had a similar requirement in SAS. The Nodes
varied to when you got to the GooooVoo number



The technique I used was to flip/revers the dataset name so A.B.C.GooooVoo was
GooooVoo.c.b.a (or maybe it was ooVooooG.c.b.a) Then the first node could be
extracted and checked to see if it was a GooooVoo number





Can something like that be done in SQL?



Lizette





From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:14 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution



Lock, Sam,



Of course, after I published a reply, a better solution popped into my head:

CASE

WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1

AND POSSTR(CANDIDATE_LEVEL,'V') = 7

AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)

,'9999999999'

,'0123456789'

)

CONCAT

TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)

,'9999999999'

,'0123456789'

)

='999999'

THEN 'IS GDG'

ELSE 'NOT GDG'

END AS GDG_FLAG



This mixes your stuff and mine and results in a precise solution with fewer
function calls than my original. Thanks for your contributions :-)



From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 12:05 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)



Lock, Sam,



I think I see a problem with that - call me a perfectionist. Also, a nit. The
order of the _from-string_ and the _to-string_ needs to be reversed, according
to the syntax diagram (P. 621, ch.4, "Built-in functions", topic TRANSLATE, in
dsnsqm0d.pdf, the V10 SQL Reference)

1) If the period, G, and V are indiscriminately translated and some rat has
put them elsewhere than they belong, this code won't find that error.

2) However, I can probably fix this with SUBSTR function to spike out the
positions for ".G" and "V" --



So, this would look like:

TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT

TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT

TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT

TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT

TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')

='999999999'



Which isn't as much of a savings as I'd hoped for, but is shorter and looks like
it's using fewer operations (TRANSLATE and SUBSTR five times each, instead of
two POSSTR, six SUBSTR, and six BETWEEN).



But even this is flawed for one case: if there's already a '9' in position 7,
that's wrong for GDG levels and this code doesn't find it. (We know that there
will not be a '9' in positions 1 or 2, unless the CANDIDATE_LEVEL isn't about a
real GDG because the low level qualifier is too short; if so, the test fails
when a period is found in some later byte of the column.)



--Phil





From: Lock Lyon [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:56 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)



Phil,



Use TRANSLATE to convert chars 0-9, 'G', and 'V' to '9', then test the string
for nines, like (*NotTested* !!):



AND TRANSLATE (SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789', '999999999999') =
'99999999'



- Lock Lyon



From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:38 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - Finding GDGs (in a column of DSNAMEs)



I have z/OS filesystem DSNAMEs in a DB2 column. Attempting to identify GDG
levels in them, I'm using this:



WITH

STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)

AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),

RIGHT(RTRIM(DSNAME),9)

FROM XXXXXX.TDSNAMES_WORKING

)

SELECT

CASE

WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1

AND POSSTR(CANDIDATE_LEVEL,'V') = 7

AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'

AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'

AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'

AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'

AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'

AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'

THEN 'IS GDG LEVEL'

ELSE 'NOT GDG LEVEL'

END AS GDG_FLAG

,CANDIDATE_BASE

,CANDIDATE_LEVEL

FROM STAGE1;



The CASE statement works, but I'm wondering if there's an easier way to identify
a particular string as numeric in SQL. Right now, I'm testing each byte to see
whether it's between 0 and 9. This works, but seems like a kind of "the hard
way" to do this.



Does anyone know of a better/faster/less-SQL way to do this?



--Phil Sevetson

x1688 (212-857-1688)

3rd Floor, Cube 223

FISA HRP Database Services





Philip Sevetson

Finding GDGs (in a column of DSNAMEs) -- blended solution
(in response to Lizette Koehler)
Lizette,

There doesn't seem to be a SQL function to reverse a string, either by "levels" or just plain mirror-flip. However, using RIGHT(string,length) definitely extracts the rightmost (length) bytes of (string), which I think accomplishes the business purpose which you're looking for (without actually reversing the string). A similar function LEFT(string,length) exists for pulling the opposite end.

The reason this works is because a genuine GDG entry ends in that eight-byte "GnnnnVnn" format, so you've got a known length to snip off for analysis.

From: Lizette Koehler [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 1:54 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution

So, I am not great with SQL, but I had a similar requirement in SAS. The Nodes varied to when you got to the GooooVoo number

The technique I used was to flip/revers the dataset name so A.B.C.GooooVoo was GooooVoo.c.b.a (or maybe it was ooVooooG.c.b.a) Then the first node could be extracted and checked to see if it was a GooooVoo number


Can something like that be done in SQL?

Lizette


From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:14 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution

Lock, Sam,

Of course, after I published a reply, a better solution popped into my head:
CASE
WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
AND POSSTR(CANDIDATE_LEVEL,'V') = 7
AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)
,'9999999999'
,'0123456789'
)
CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)
,'9999999999'
,'0123456789'
)
='999999'
THEN 'IS GDG'
ELSE 'NOT GDG'
END AS GDG_FLAG

This mixes your stuff and mine and results in a precise solution with fewer function calls than my original. Thanks for your contributions :-)

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 12:05 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)

Lock, Sam,

I think I see a problem with that - call me a perfectionist. Also, a nit. The order of the _from-string_ and the _to-string_ needs to be reversed, according to the syntax diagram (P. 621, ch.4, "Built-in functions", topic TRANSLATE, in dsnsqm0d.pdf, the V10 SQL Reference)

1) If the period, G, and V are indiscriminately translated and some rat has put them elsewhere than they belong, this code won't find that error.

2) However, I can probably fix this with SUBSTR function to spike out the positions for ".G" and "V" --

So, this would look like:
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')
='999999999'

Which isn't as much of a savings as I'd hoped for, but is shorter and looks like it's using fewer operations (TRANSLATE and SUBSTR five times each, instead of two POSSTR, six SUBSTR, and six BETWEEN).

But even this is flawed for one case: if there's already a '9' in position 7, that's wrong for GDG levels and this code doesn't find it. (We know that there will not be a '9' in positions 1 or 2, unless the CANDIDATE_LEVEL isn't about a real GDG because the low level qualifier is too short; if so, the test fails when a period is found in some later byte of the column.)

--Phil


From: Lock Lyon [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:56 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)

Phil,

Use TRANSLATE to convert chars 0-9, 'G', and 'V' to '9', then test the string for nines, like (*NotTested* !!):

AND TRANSLATE (SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789', '999999999999') = '99999999'

- Lock Lyon

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:38 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Finding GDGs (in a column of DSNAMEs)

I have z/OS filesystem DSNAMEs in a DB2 column. Attempting to identify GDG levels in them, I'm using this:

WITH
STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)
AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),
RIGHT(RTRIM(DSNAME),9)
FROM XXXXXX.TDSNAMES_WORKING
)
SELECT
CASE
WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
AND POSSTR(CANDIDATE_LEVEL,'V') = 7
AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'
THEN 'IS GDG LEVEL'
ELSE 'NOT GDG LEVEL'
END AS GDG_FLAG
,CANDIDATE_BASE
,CANDIDATE_LEVEL
FROM STAGE1;

The CASE statement works, but I'm wondering if there's an easier way to identify a particular string as numeric in SQL. Right now, I'm testing each byte to see whether it's between 0 and 9. This works, but seems like a kind of "the hard way" to do this.

Does anyone know of a better/faster/less-SQL way to do this?

--Phil Sevetson
x1688 (212-857-1688)
3rd Floor, Cube 223
FISA HRP Database Services



-----End Original Message-----

Muthuraj Kumaresan

Finding GDGs (in a column of DSNAMEs) -- blended solution
(in response to Philip Sevetson)
Will this help?


CASE WHEN TRANSLATE(CANDIDATE_LEVEL
> ,'9999999999'
> ,'0123456789'
> ) = '.G9999V99' THEN 'GDG'
ELSE 'NOT GDG' END AS GDG_FLAG



Sent from my iPhone

> On 16 Dec 2016, at 12:46 AM, Sevetson, Phil <[login to unmask email]> wrote:
>
> Lizette,
>
> There doesn’t seem to be a SQL function to reverse a string, either by “levels” or just plain mirror-flip. However, using RIGHT(string,length) definitely extracts the rightmost (length) bytes of (string), which I think accomplishes the business purpose which you’re looking for (without actually reversing the string). A similar function LEFT(string,length) exists for pulling the opposite end.
>
> The reason this works is because a genuine GDG entry ends in that eight-byte “GnnnnVnn” format, so you’ve got a known length to snip off for analysis.
>
> From: Lizette Koehler [mailto:[login to unmask email]
> Sent: Thursday, December 15, 2016 1:54 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution
>
> So, I am not great with SQL, but I had a similar requirement in SAS. The Nodes varied to when you got to the GooooVoo number
>
> The technique I used was to flip/revers the dataset name so A.B.C.GooooVoo was GooooVoo.c.b.a (or maybe it was ooVooooG.c.b.a) Then the first node could be extracted and checked to see if it was a GooooVoo number
>
>
> Can something like that be done in SQL?
>
> Lizette
>
>
> From: Sevetson, Phil [mailto:[login to unmask email]
> Sent: Thursday, December 15, 2016 10:14 AM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution
>
> Lock, Sam,
>
> Of course, after I published a reply, a better solution popped into my head:
> CASE
> WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
> AND POSSTR(CANDIDATE_LEVEL,'V') = 7
> AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)
> ,'9999999999'
> ,'0123456789'
> )
> CONCAT
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)
> ,'9999999999'
> ,'0123456789'
> )
> ='999999'
> THEN 'IS GDG'
> ELSE 'NOT GDG'
> END AS GDG_FLAG
>
> This mixes your stuff and mine and results in a precise solution with fewer function calls than my original. Thanks for your contributions :-)
>
> From: Sevetson, Phil [mailto:[login to unmask email]
> Sent: Thursday, December 15, 2016 12:05 PM
> To: '[login to unmask email]'
> Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)
>
> Lock, Sam,
>
> I think I see a problem with that – call me a perfectionist. Also, a nit. The order of the _from-string_ and the _to-string_ needs to be reversed, according to the syntax diagram (P. 621, ch.4, “Built-in functions”, topic TRANSLATE, in dsnsqm0d.pdf, the V10 SQL Reference)
> 1) If the period, G, and V are indiscriminately translated and some rat has put them elsewhere than they belong, this code won’t find that error.
> 2) However, I can probably fix this with SUBSTR function to spike out the positions for “.G” and “V” --
>
> So, this would look like:
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')
> ='999999999'
>
> Which isn’t as much of a savings as I’d hoped for, but is shorter and looks like it’s using fewer operations (TRANSLATE and SUBSTR five times each, instead of two POSSTR, six SUBSTR, and six BETWEEN).
>
> But even this is flawed for one case: if there’s already a ‘9’ in position 7, that’s wrong for GDG levels and this code doesn’t find it. (We know that there will not be a ‘9’ in positions 1 or 2, unless the CANDIDATE_LEVEL isn’t about a real GDG because the low level qualifier is too short; if so, the test fails when a period is found in some later byte of the column.)
>
> --Phil
>
>
> From: Lock Lyon [mailto:[login to unmask email]
> Sent: Thursday, December 15, 2016 10:56 AM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)
>
> Phil,
>
> Use TRANSLATE to convert chars 0-9, 'G', and 'V' to '9', then test the string for nines, like (*NotTested* !!):
>
> AND TRANSLATE (SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789', '999999999999') = '99999999'
>
> - Lock Lyon
>
> From: Sevetson, Phil [mailto:[login to unmask email]
> Sent: Thursday, December 15, 2016 10:38 AM
> To: [login to unmask email]
> Subject: [DB2-L] - Finding GDGs (in a column of DSNAMEs)
>
> I have z/OS filesystem DSNAMEs in a DB2 column. Attempting to identify GDG levels in them, I’m using this:
>
> WITH
> STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)
> AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),
> RIGHT(RTRIM(DSNAME),9)
> FROM XXXXXX.TDSNAMES_WORKING
> )
> SELECT
> CASE
> WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
> AND POSSTR(CANDIDATE_LEVEL,'V') = 7
> AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'
> THEN 'IS GDG LEVEL'
> ELSE 'NOT GDG LEVEL'
> END AS GDG_FLAG
> ,CANDIDATE_BASE
> ,CANDIDATE_LEVEL
> FROM STAGE1;
>
> The CASE statement works, but I’m wondering if there’s an easier way to identify a particular string as numeric in SQL. Right now, I’m testing each byte to see whether it’s between 0 and 9. This works, but seems like a kind of “the hard way” to do this.
>
> Does anyone know of a better/faster/less-SQL way to do this?
>
> --Phil Sevetson
> x1688 (212-857-1688)
> 3rd Floor, Cube 223
> FISA HRP Database Services
>
>
>
> -----End Original Message-----
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
> ---> Anaheim, California, April 30 - May 04, 2017 <---
> http://www.idug.org/na
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Sam Baugh

Finding GDGs (in a column of DSNAMEs) -- blended solution
(in response to Philip Sevetson)
can replace the POSSTR's with LIKE '.G____V%'

On Thu, Dec 15, 2016 at 11:14 AM, Sevetson, Phil <[login to unmask email]>
wrote:

> Lock, Sam,
>
>
>
> Of course, after I published a reply, a better solution popped into my
> head:
>
> CASE
>
> WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
>
> AND POSSTR(CANDIDATE_LEVEL,'V') = 7
>
> AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)
>
> ,'9999999999'
>
> ,'0123456789'
>
> )
>
> CONCAT
>
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)
>
> ,'9999999999'
>
> ,'0123456789'
>
> )
>
> ='999999'
>
> THEN 'IS GDG'
>
> ELSE 'NOT GDG'
>
> END AS GDG_FLAG
>
>
>
> This mixes your stuff and mine and results in a precise solution with
> fewer function calls than my original. Thanks for your contributions :-)
>
>
>
> *From:* Sevetson, Phil [mailto:[login to unmask email]
> *Sent:* Thursday, December 15, 2016 12:05 PM
> *To:* '[login to unmask email]'
> *Subject:* [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)
>
>
>
> Lock, Sam,
>
>
>
> I think I see a problem with that – call me a perfectionist. Also, a nit.
> The order of the _*from-string*_ and the _*to-string*_ needs to be
> reversed, according to the syntax diagram (P. 621, ch.4, “Built-in
> functions”, topic TRANSLATE, in dsnsqm0d.pdf, the V10 SQL Reference)
>
> 1) If the period, G, and V are indiscriminately translated and some
> rat has put them elsewhere than they belong, this code won’t find that
> error.
>
> 2) However, I can probably fix this with SUBSTR function to spike
> out the positions for “.G” and “V” --
>
>
>
> So, this would look like:
>
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT
>
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT
>
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT
>
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT
>
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')
>
> ='999999999'
>
>
>
> Which isn’t as much of a savings as I’d hoped for, but is shorter and
> looks like it’s using fewer operations (TRANSLATE and SUBSTR five times
> each, instead of two POSSTR, six SUBSTR, and six BETWEEN).
>
>
>
> But even this is flawed for one case: if there’s already a ‘9’ in position
> 7, that’s wrong for GDG levels and this code doesn’t find it. (We know that
> there will not be a ‘9’ in positions 1 or 2, unless the CANDIDATE_LEVEL
> isn’t about a real GDG because the low level qualifier is too short; if so,
> the test fails when a period is found in some later byte of the column.)
>
>
>
> --Phil
>
>
>
>
>
> *From:* Lock Lyon [mailto:[login to unmask email] <[login to unmask email]>]
> *Sent:* Thursday, December 15, 2016 10:56 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)
>
>
>
> Phil,
>
>
>
> Use TRANSLATE to convert chars 0-9, 'G', and 'V' to '9', then test the
> string for nines, like (*NotTested* !!):
>
>
>
> AND TRANSLATE (SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789',
> '999999999999') = '99999999'
>
>
>
> - Lock Lyon
>
>
>
> *From:* Sevetson, Phil [mailto:[login to unmask email] <[login to unmask email]>]
>
> *Sent:* Thursday, December 15, 2016 10:38 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Finding GDGs (in a column of DSNAMEs)
>
>
>
> I have z/OS filesystem DSNAMEs in a DB2 column. Attempting to identify GDG
> levels in them, I’m using this:
>
>
>
> WITH
>
> STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)
>
> AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),
>
> RIGHT(RTRIM(DSNAME),9)
>
> FROM XXXXXX.TDSNAMES_WORKING
>
> )
>
> SELECT
>
> CASE
>
> WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
>
> AND POSSTR(CANDIDATE_LEVEL,'V') = 7
>
> AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'
>
> AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'
>
> AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'
>
> AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'
>
> AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'
>
> AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'
>
> THEN 'IS GDG LEVEL'
>
> ELSE 'NOT GDG LEVEL'
>
> END AS GDG_FLAG
>
> ,CANDIDATE_BASE
>
> ,CANDIDATE_LEVEL
>
> FROM STAGE1;
>
>
>
> The CASE statement works, but I’m wondering if there’s an easier way to
> identify a particular string as numeric in SQL. Right now, I’m testing each
> byte to see whether it’s between 0 and 9. This works, but seems like a kind
> of “the hard way” to do this.
>
>
>
> Does anyone know of a better/faster/less-SQL way to do this?
>
>
>
> --Phil Sevetson
>
> x1688 (212-857-1688)
>
> 3rd Floor, Cube 223
>
> FISA HRP Database Services
>
>
>
>
>
> -----End Original Message-----
>
>
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

Philip Sevetson

Finding GDGs (in a column of DSNAMEs) -- blended solution
(in response to Muthuraj Kumaresan)
I love it. MUCH simpler. Thank you!

From: Muthu [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 2:41 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution

Will this help?


CASE WHEN TRANSLATE(CANDIDATE_LEVEL
,'9999999999'
,'0123456789'
) = '.G9999V99' THEN 'GDG'
ELSE 'NOT GDG' END AS GDG_FLAG



Sent from my iPhone

On 16 Dec 2016, at 12:46 AM, Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Lizette,

There doesn’t seem to be a SQL function to reverse a string, either by “levels” or just plain mirror-flip. However, using RIGHT(string,length) definitely extracts the rightmost (length) bytes of (string), which I think accomplishes the business purpose which you’re looking for (without actually reversing the string). A similar function LEFT(string,length) exists for pulling the opposite end.

The reason this works is because a genuine GDG entry ends in that eight-byte “GnnnnVnn” format, so you’ve got a known length to snip off for analysis.

From: Lizette Koehler [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 1:54 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution

So, I am not great with SQL, but I had a similar requirement in SAS. The Nodes varied to when you got to the GooooVoo number

The technique I used was to flip/revers the dataset name so A.B.C.GooooVoo was GooooVoo.c.b.a (or maybe it was ooVooooG.c.b.a) Then the first node could be extracted and checked to see if it was a GooooVoo number


Can something like that be done in SQL?

Lizette


From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:14 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution

Lock, Sam,

Of course, after I published a reply, a better solution popped into my head:
CASE
WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
AND POSSTR(CANDIDATE_LEVEL,'V') = 7
AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)
,'9999999999'
,'0123456789'
)
CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)
,'9999999999'
,'0123456789'
)
='999999'
THEN 'IS GDG'
ELSE 'NOT GDG'
END AS GDG_FLAG

This mixes your stuff and mine and results in a precise solution with fewer function calls than my original. Thanks for your contributions :-)

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 12:05 PM
To: '[login to unmask email]<mailto:[login to unmask email]>'
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)

Lock, Sam,

I think I see a problem with that – call me a perfectionist. Also, a nit. The order of the _from-string_ and the _to-string_ needs to be reversed, according to the syntax diagram (P. 621, ch.4, “Built-in functions”, topic TRANSLATE, in dsnsqm0d.pdf, the V10 SQL Reference)

1) If the period, G, and V are indiscriminately translated and some rat has put them elsewhere than they belong, this code won’t find that error.

2) However, I can probably fix this with SUBSTR function to spike out the positions for “.G” and “V” --

So, this would look like:
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')
='999999999'

Which isn’t as much of a savings as I’d hoped for, but is shorter and looks like it’s using fewer operations (TRANSLATE and SUBSTR five times each, instead of two POSSTR, six SUBSTR, and six BETWEEN).

But even this is flawed for one case: if there’s already a ‘9’ in position 7, that’s wrong for GDG levels and this code doesn’t find it. (We know that there will not be a ‘9’ in positions 1 or 2, unless the CANDIDATE_LEVEL isn’t about a real GDG because the low level qualifier is too short; if so, the test fails when a period is found in some later byte of the column.)

--Phil


From: Lock Lyon [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:56 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)

Phil,

Use TRANSLATE to convert chars 0-9, 'G', and 'V' to '9', then test the string for nines, like (*NotTested* !!):

AND TRANSLATE (SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789', '999999999999') = '99999999'

- Lock Lyon

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:38 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Finding GDGs (in a column of DSNAMEs)

I have z/OS filesystem DSNAMEs in a DB2 column. Attempting to identify GDG levels in them, I’m using this:

WITH
STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)
AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),
RIGHT(RTRIM(DSNAME),9)
FROM XXXXXX.TDSNAMES_WORKING
)
SELECT
CASE
WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
AND POSSTR(CANDIDATE_LEVEL,'V') = 7
AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'
THEN 'IS GDG LEVEL'
ELSE 'NOT GDG LEVEL'
END AS GDG_FLAG
,CANDIDATE_BASE
,CANDIDATE_LEVEL
FROM STAGE1;

The CASE statement works, but I’m wondering if there’s an easier way to identify a particular string as numeric in SQL. Right now, I’m testing each byte to see whether it’s between 0 and 9. This works, but seems like a kind of “the hard way” to do this.

Does anyone know of a better/faster/less-SQL way to do this?

--Phil Sevetson
x1688 (212-857-1688)
3rd Floor, Cube 223
FISA HRP Database Services



-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

Philip Sevetson

Finding GDGs (in a column of DSNAMEs) -- blended solution
(in response to Sam Baugh)
I like that one, too, but Muthu has collapsed it all into a single function call which precisely states the requirements of the GDG Level DSN low-order qualifier. Also, shorter and clearer. This gets second prize, I think, and is an improvement to the last one which I put up.

From: Sam Baugh [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 3:03 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution

can replace the POSSTR's with LIKE '.G____V%'

On Thu, Dec 15, 2016 at 11:14 AM, Sevetson, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Lock, Sam,

Of course, after I published a reply, a better solution popped into my head:
CASE
WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
AND POSSTR(CANDIDATE_LEVEL,'V') = 7
AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)
,'9999999999'
,'0123456789'
)
CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)
,'9999999999'
,'0123456789'
)
='999999'
THEN 'IS GDG'
ELSE 'NOT GDG'
END AS GDG_FLAG

This mixes your stuff and mine and results in a precise solution with fewer function calls than my original. Thanks for your contributions :-)

From: Sevetson, Phil [mailto:[login to unmask email]<mailto:[login to unmask email]>]
Sent: Thursday, December 15, 2016 12:05 PM
To: '[login to unmask email]<mailto:[login to unmask email]>'
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)

Lock, Sam,

I think I see a problem with that – call me a perfectionist. Also, a nit. The order of the _from-string_ and the _to-string_ needs to be reversed, according to the syntax diagram (P. 621, ch.4, “Built-in functions”, topic TRANSLATE, in dsnsqm0d.pdf, the V10 SQL Reference)

1) If the period, G, and V are indiscriminately translated and some rat has put them elsewhere than they belong, this code won’t find that error.

2) However, I can probably fix this with SUBSTR function to spike out the positions for “.G” and “V” --

So, this would look like:
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')
='999999999'

Which isn’t as much of a savings as I’d hoped for, but is shorter and looks like it’s using fewer operations (TRANSLATE and SUBSTR five times each, instead of two POSSTR, six SUBSTR, and six BETWEEN).

But even this is flawed for one case: if there’s already a ‘9’ in position 7, that’s wrong for GDG levels and this code doesn’t find it. (We know that there will not be a ‘9’ in positions 1 or 2, unless the CANDIDATE_LEVEL isn’t about a real GDG because the low level qualifier is too short; if so, the test fails when a period is found in some later byte of the column.)

--Phil


From: Lock Lyon [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:56 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)

Phil,

Use TRANSLATE to convert chars 0-9, 'G', and 'V' to '9', then test the string for nines, like (*NotTested* !!):

AND TRANSLATE (SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789', '999999999999') = '99999999'

- Lock Lyon

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:38 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Finding GDGs (in a column of DSNAMEs)

I have z/OS filesystem DSNAMEs in a DB2 column. Attempting to identify GDG levels in them, I’m using this:

WITH
STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)
AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),
RIGHT(RTRIM(DSNAME),9)
FROM XXXXXX.TDSNAMES_WORKING
)
SELECT
CASE
WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
AND POSSTR(CANDIDATE_LEVEL,'V') = 7
AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'
THEN 'IS GDG LEVEL'
ELSE 'NOT GDG LEVEL'
END AS GDG_FLAG
,CANDIDATE_BASE
,CANDIDATE_LEVEL
FROM STAGE1;

The CASE statement works, but I’m wondering if there’s an easier way to identify a particular string as numeric in SQL. Right now, I’m testing each byte to see whether it’s between 0 and 9. This works, but seems like a kind of “the hard way” to do this.

Does anyone know of a better/faster/less-SQL way to do this?

--Phil Sevetson
x1688 (212-857-1688)
3rd Floor, Cube 223
FISA HRP Database Services


-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

Larry Kintisch

Finding GDGs (in a column of DSNAMEs) -- blended solution
(in response to Lizette Koehler)
Hi Liz,
A solution to DS name complete reversal would
be an Assembler language UDF or Stored procedure
that utilizes the Z/series TRANSLATE instruction
in an unique "backwards" way. I first discovered
this when I was an IBM Systems Engineer in S/360 days.

TR D1(L,B1),D2(B2) You place your full ds
name in the location D2(B2) -- lets use a 4-byte
example "ABCD" Then build a reverse string of hex
bytes in D1(B1) that is as long, L, as the max DS
name, in this case, "4" that would be X'03020100'
Executing TR produces in D1(B1) "DCBA".

Then use SQL STRIP etc to finish the job!

Larry Kintisch Pres., ABLE Information Services [login to unmask email]

At 13:54 2016-12-15, you wrote:
>So, I am not great with SQL, but I had a similar
>requirement in SAS. The Nodes varied to when you got to the GooooVoo number
>
>The technique I used was to flip/revers the
>dataset name so A.B.C.GooooVoo was
>GooooVoo.c.b.a (or maybe it was
>ooVooooG.c.b.a) Then the first node could be
>extracted and checked to see if it was a GooooVoo number
>
>
>Can something like that be done in SQL?
>
>Lizette
>
>
>From: Sevetson, Phil [mailto:[login to unmask email]
>Sent: Thursday, December 15, 2016 10:14 AM
>To: [login to unmask email]
>Subject: [DB2-L] - RE: Finding GDGs (in a column
>of DSNAMEs) -- blended solution
>
>Lock, Sam,
>
>Of course, after I published a reply, a better solution popped into my head:
>CASE
> WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
> AND POSSTR(CANDIDATE_LEVEL,'V') = 7
> AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)
> ,'9999999999'
> ,'0123456789'
> )
> CONCAT
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)
> ,'9999999999'
> ,'0123456789'
> )
> ='999999'
> THEN 'IS GDG'
> ELSE 'NOT GDG'
>END AS GDG_FLAG
>
>This mixes your stuff and mine and results in a
>precise solution with fewer function calls than
>my original. Thanks for your contributions :-)
>
>From: Sevetson, Phil
>[<mailto:[login to unmask email]>mailto:[login to unmask email]
>Sent: Thursday, December 15, 2016 12:05 PM
>To: '[login to unmask email]'
>Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)
>
>Lock, Sam,
>
>I think I see a problem with that – call me a
>perfectionist. Also, a nit. The order of the
>_from-string_ and the _to-string_ needs to be
>reversed, according to the syntax diagram (P.
>621, ch.4, “Built-in functions”, topic
>TRANSLATE, in dsnsqm0d.pdf, the V10 SQL Reference)
>1) If the period, G, and V are
>indiscriminately translated and some rat has put
>them elsewhere than they belong, this code won’t find that error.
>2) However, I can probably fix this with
>SUBSTR function to spike out the positions for “.G” and “V” --
>
>So, this would look like:
>TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT
>TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT
>TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT
>TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT
>TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')
>='999999999'
>
>Which isn’t as much of a savings as I’d hoped
>for, but is shorter and looks like it’s using
>fewer operations (TRANSLATE and SUBSTR five
>times each, instead of two POSSTR, six SUBSTR, and six BETWEEN).
>
>But even this is flawed for one case: if there’s
>already a ‘9’ in position 7, that’s wrong for
>GDG levels and this code doesn’t find it. (We
>know that there will not be a ‘9’ in positions 1
>or 2, unless the CANDIDATE_LEVEL isn’t about a
>real GDG because the low level qualifier is too
>short; if so, the test fails when a period is
>found in some later byte of the column.)
>
>--Phil
>
>
>From: Lock Lyon [<mailto:[login to unmask email]>mailto:[login to unmask email]
>Sent: Thursday, December 15, 2016 10:56 AM
>To: <mailto:[login to unmask email]>[login to unmask email]
>Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)
>
>Phil,
>
>Use TRANSLATE to convert chars 0-9, 'G', and 'V'
>to '9', then test the string for nines, like (*NotTested* !!):
>
>AND TRANSLATE
>(SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789', '999999999999') = '99999999'
>
>- Lock Lyon
>
>From: Sevetson, Phil
>[<mailto:[login to unmask email]>mailto:[login to unmask email]
>Sent: Thursday, December 15, 2016 10:38 AM
>To: <mailto:[login to unmask email]>[login to unmask email]
>Subject: [DB2-L] - Finding GDGs (in a column of DSNAMEs)
>
>I have z/OS filesystem DSNAMEs in a DB2 column.
>Attempting to identify GDG levels in them, I’m using this:
>
>WITH
>STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)
>AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),
> RIGHT(RTRIM(DSNAME),9)
> FROM XXXXXX.TDSNAMES_WORKING
> )
>SELECT
> CASE
> WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
> AND POSSTR(CANDIDATE_LEVEL,'V') = 7
> AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'
> THEN 'IS GDG LEVEL'
> ELSE 'NOT GDG LEVEL'
>END AS GDG_FLAG
> ,CANDIDATE_BASE
> ,CANDIDATE_LEVEL
>FROM STAGE1;
>
>The CASE statement works, but I’m wondering if
>there’s an easier way to identify a particular
>string as numeric in SQL. Right now, I’m testing
>each byte to see whether it’s between 0 and 9.
>This works, but seems like a kind of “the hard way” to do this.
>
>Does anyone know of a better/faster/less-SQL way to do this?
>
>--Phil Sevetson
>x1688 (212-857-1688)
>3rd Floor, Cube 223
>FISA HRP Database Services
>
>
>
>Site Links:
><http://www.idug.org/p/fo/st/post=179373&anc=p179373#p179373>View
>post
>online
><http://www.idug.org/p/fo/si/topic=19>View
>mailing list
>online <mailto:[login to unmask email]>Start new
>thread via
>email
><mailto:[login to unmask email]?Subject=Unsubscribe>Unsubscribe
>from this mailing
>list http://www.idug.org/p/us/toManage your
>subscription This email has been sent to: [login to unmask email]
>
>** ** ** Attend the 2017 IDUG Tech Conference
>North America ** ** ** ---> Anaheim, California,
>April 30 - May 04, 2017 <--- <http://www.idug.org/na>http://www.idug.org/na
>
>
>Use of this email content is governed by the terms of service at:
><http://www.idug.org/p/cm/ld/fid=2>http://www.idug.org/p/cm/ld/fid=2

Venkat Srinivasan

RE: Finding GDGs (in a column of DSNAMEs) -- blended solution
(in response to Larry Kintisch)

Why not keep it simple and choose mvcin instruction to reverse? Not quite following the TR setup discussed. The D2(B2) you refer is 256 bytes long and subject to page boundary checks. Are you dynamically building it each time?

Venkat

In Reply to Larry Kintisch:

Hi Liz,
A solution to DS name complete reversal would
be an Assembler language UDF or Stored procedure
that utilizes the Z/series TRANSLATE instruction
in an unique "backwards" way. I first discovered
this when I was an IBM Systems Engineer in S/360 days.

TR D1(L,B1),D2(B2) You place your full ds
name in the location D2(B2) -- lets use a 4-byte
example "ABCD" Then build a reverse string of hex
bytes in D1(B1) that is as long, L, as the max DS
name, in this case, "4" that would be X'03020100'
Executing TR produces in D1(B1) "DCBA".

Then use SQL STRIP etc to finish the job!

Larry Kintisch Pres., ABLE Information Services [login to unmask email]

At 13:54 2016-12-15, you wrote:
>So, I am not great with SQL, but I had a similar
>requirement in SAS. The Nodes varied to when you got to the GooooVoo number
>
>The technique I used was to flip/revers the
>dataset name so A.B.C.GooooVoo was
>GooooVoo.c.b.a (or maybe it was
>ooVooooG.c.b.a) Then the first node could be
>extracted and checked to see if it was a GooooVoo number
>
>
>Can something like that be done in SQL?
>
>Lizette
>
>
>From: Sevetson, Phil [mailto:[login to unmask email]
>Sent: Thursday, December 15, 2016 10:14 AM
>To: [login to unmask email]
>Subject: [DB2-L] - RE: Finding GDGs (in a column
>of DSNAMEs) -- blended solution
>
>Lock, Sam,
>
>Of course, after I published a reply, a better solution popped into my head:
>CASE
> WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
> AND POSSTR(CANDIDATE_LEVEL,'V') = 7
> AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)
> ,'9999999999'
> ,'0123456789'
> )
> CONCAT
> TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)
> ,'9999999999'
> ,'0123456789'
> )
> ='999999'
> THEN 'IS GDG'
> ELSE 'NOT GDG'
>END AS GDG_FLAG
>
>This mixes your stuff and mine and results in a
>precise solution with fewer function calls than
>my original. Thanks for your contributions :-)
>
>From: Sevetson, Phil
>[<mailto:[login to unmask email]>mailto:[login to unmask email]
>Sent: Thursday, December 15, 2016 12:05 PM
>To: '[login to unmask email]'
>Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)
>
>Lock, Sam,
>
>I think I see a problem with that – call me a
>perfectionist. Also, a nit. The order of the
>_from-string_ and the _to-string_ needs to be
>reversed, according to the syntax diagram (P.
>621, ch.4, “Built-in functions”, topic
>TRANSLATE, in dsnsqm0d.pdf, the V10 SQL Reference)
>1) If the period, G, and V are
>indiscriminately translated and some rat has put
>them elsewhere than they belong, this code won’t find that error.
>2) However, I can probably fix this with
>SUBSTR function to spike out the positions for “.G” and “V” --
>
>So, this would look like:
>TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT
>TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT
>TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT
>TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT
>TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')
>='999999999'
>
>Which isn’t as much of a savings as I’d hoped
>for, but is shorter and looks like it’s using
>fewer operations (TRANSLATE and SUBSTR five
>times each, instead of two POSSTR, six SUBSTR, and six BETWEEN).
>
>But even this is flawed for one case: if there’s
>already a ‘9’ in position 7, that’s wrong for
>GDG levels and this code doesn’t find it. (We
>know that there will not be a ‘9’ in positions 1
>or 2, unless the CANDIDATE_LEVEL isn’t about a
>real GDG because the low level qualifier is too
>short; if so, the test fails when a period is
>found in some later byte of the column.)
>
>--Phil
>
>
>From: Lock Lyon [<mailto:[login to unmask email]>mailto:[login to unmask email]
>Sent: Thursday, December 15, 2016 10:56 AM
>To: <mailto:[login to unmask email]>[login to unmask email]
>Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)
>
>Phil,
>
>Use TRANSLATE to convert chars 0-9, 'G', and 'V'
>to '9', then test the string for nines, like (*NotTested* !!):
>
>AND TRANSLATE
>(SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789', '999999999999') = '99999999'
>
>- Lock Lyon
>
>From: Sevetson, Phil
>[<mailto:[login to unmask email]>mailto:[login to unmask email]
>Sent: Thursday, December 15, 2016 10:38 AM
>To: <mailto:[login to unmask email]>[login to unmask email]
>Subject: [DB2-L] - Finding GDGs (in a column of DSNAMEs)
>
>I have z/OS filesystem DSNAMEs in a DB2 column.
>Attempting to identify GDG levels in them, I’m using this:
>
>WITH
>STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)
>AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),
> RIGHT(RTRIM(DSNAME),9)
> FROM XXXXXX.TDSNAMES_WORKING
> )
>SELECT
> CASE
> WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
> AND POSSTR(CANDIDATE_LEVEL,'V') = 7
> AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'
> AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'
> THEN 'IS GDG LEVEL'
> ELSE 'NOT GDG LEVEL'
>END AS GDG_FLAG
> ,CANDIDATE_BASE
> ,CANDIDATE_LEVEL
>FROM STAGE1;
>
>The CASE statement works, but I’m wondering if
>there’s an easier way to identify a particular
>string as numeric in SQL. Right now, I’m testing
>each byte to see whether it’s between 0 and 9.
>This works, but seems like a kind of “the hard way” to do this.
>
>Does anyone know of a better/faster/less-SQL way to do this?
>
>--Phil Sevetson
>x1688 (212-857-1688)
>3rd Floor, Cube 223
>FISA HRP Database Services
>
>
>
>Site Links:
><http://www.idug.org/p/fo/st/post=179373&anc=p179373#p179373>View
>post
>online
><http://www.idug.org/p/fo/si/topic=19>View
>mailing list
>online <mailto:[login to unmask email]>Start new
>thread via
>email
><mailto:[login to unmask email]?Subject=Unsubscribe>Unsubscribe
>from this mailing
>list http://www.idug.org/p/us/toManage your
>subscription This email has been sent to: [login to unmask email]
>
>** ** ** Attend the 2017 IDUG Tech Conference
>North America ** ** ** ---> Anaheim, California,
>April 30 - May 04, 2017 http://www.idug.org/na>http://www.idug.org/na
>
>
>Use of this email content is governed by the terms of service at:
><http://www.idug.org/p/cm/ld/fid=2>http://www.idug.org/p/cm/ld/fid=2

Larry Kintisch

Finding GDGs (in a column of DSNAMEs) -- blended solution
(in response to Venkat Srinivasan)
Hi Venkat,
You are correct. MOVE INVERSE MVCIN
D1(L1,B1),D2(B2) is a "Z/series" instruction for
reversing a string of up to 255 bytes. S/360 did
not have that instruction, and I have never tried
to keep up with Principles of Operations
[Assembler] instructions since the time of "the yellow card."

Larry Kintisch

At 17:48 2016-12-15, you wrote:

>Why not keep it simple and choose mvcin
>instruction to reverse? Not quite following the
>TR setup discussed. The D2(B2) you refer is 256
>bytes long and subject to page boundary checks.
>Are you dynamically building it each time?
>
>Venkat
>
>In Reply to Larry Kintisch:
>Hi Liz,
>A solution to DS name complete reversal would
>be an Assembler language UDF or Stored procedure
>that utilizes the Z/series TRANSLATE instruction
>in an unique "backwards" way. I first discovered
>this when I was an IBM Systems Engineer in S/360 days.
>
>TR D1(L,B1),D2(B2) You place your full ds
>name in the location D2(B2) -- lets use a 4-byte
>example "ABCD" Then build a reverse string of hex
>bytes in D1(B1) that is as long, L, as the max DS
>name, in this case, "4" that would be X'03020100'
>Executing TR produces in D1(B1) "DCBA".
>
>Then use SQL STRIP etc to finish the job!
>
>Larry Kintisch Pres., ABLE Information Services [login to unmask email]
>
>At 13:54 2016-12-15, you wrote:
> >So, I am not great with SQL, but I had a similar
> >requirement in SAS. The Nodes varied to when you got to the GooooVoo number
> >
> >The technique I used was to flip/revers the
> >dataset name so A.B.C.GooooVoo was
> >GooooVoo.c.b.a (or maybe it was
> >ooVooooG.c.b.a) Then the first node could be
> >extracted and checked to see if it was a GooooVoo number
> >
> >
> >Can something like that be done in SQL?
> >
> >Lizette
> >
> >
> >From: Sevetson, Phil
> [<mailto:[login to unmask email]>mailto:[login to unmask email]
> >Sent: Thursday, December 15, 2016 10:14 AM
> >To: [login to unmask email]
> >Subject: [DB2-L] - RE: Finding GDGs (in a column
> >of DSNAMEs) -- blended solution
> >
> >Lock, Sam,
> >
> >Of course, after I published a reply, a better solution popped into my head:
> >CASE
> > WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
> > AND POSSTR(CANDIDATE_LEVEL,'V') = 7
> > AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)
> > ,'9999999999'
> > ,'0123456789'
> > )
> > CONCAT
> > TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)
> > ,'9999999999'
> > ,'0123456789'
> > )
> > ='999999'
> > THEN 'IS GDG'
> > ELSE 'NOT GDG'
> >END AS GDG_FLAG
> >
> >This mixes your stuff and mine and results in a
> >precise solution with fewer function calls than
> >my original. Thanks for your contributions :-)
> >
> >From: Sevetson, Phil
> >[<<mailto:[login to unmask email]>mailto:[login to unmask email]
> ts.idug.org]>mailto:[login to unmask email]>mailto:[login to unmask email]
> >Sent: Thursday, December 15, 2016 12:05 PM
> >To: '[login to unmask email]'
> >Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)
> >
> >Lock, Sam,
> >
> >I think I see a problem with that – call me a
> >perfeectionist. Also, a nit. The order of the
> >_from-string_ and the _to-string_ needs to be
> >reversed, according to the syntax diagram (P.
> >621, ch.4, â?œBuilt-in functionsâ?, topic
> >TRANSLATE, in dsnsqm0d.pdf, the V10 SQL Reference)
> >1) If the period, G, and V are
> >indiscriminately translated and some rat has put
> >them elsewhere than they belong, this code wonâ?™t find that error.
> >2) However, I can probably fix this with
> >SUBSTR function to spike out the positions for â?œ.Gâ? and â?œVâ? --
> >
> >So, this would look like:
> >TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT
> >TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT
> >TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT
> >TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT
> >TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')
> >='999999999'
> >
> >Which isnâ?™t as much of a savings as Iâ?™d hoped
> >for, but is shorter and looks like itâ?™s using
> >fewer operations (TRANSLATE and SUBSTR five
> >times each, instead of two POSSTR, six SUBSTR, and six BETWEEN).
> >
> >But even this is flawed for one case: if thereâ?™s
> >already a â?˜9â?™ in position 7, thatâ?™s wrong for
> >GDG levels and this code doesnâ?™t find it. (We
> >know that there will not be a â?˜9â?™ in positions 1
> >or 2, unless the CANDIDATE_LEVEL isnâ?™t about a
> >real GDG because the low level qualifier is too
> >short; if so, the test fails when a period is
> >found in some later byte of the column.)
> >
> >--Phil
> >
> >
> >From: Lock Lyon
> [<<mailto:[login to unmask email]>mailto:[login to unmask email]>mailto:[login to unmask email]>mailto:[login to unmask email]
> >Sent: Thursday, December 15, 2016 10:56 AM
> >To:
> <<mailto:[login to unmask email]>[login to unmask email]>mailto:[login to unmask email]>[login to unmask email]
> >Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)
> >
> >Phil,
> >
> >Use TRANSLATE to convert chars 0-9, 'G', and 'V'
> >to '9', then test the string for nines, like (*NotTested* !!):
> >
> >AND TRANSLATE
> >(SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789', '999999999999') = '99999999'
> >
> >- Lock Lyon
> >
> >From: Sevetson, Phil
> >[<<mailto:[login to unmask email]>mailto:[login to unmask email]
> ts.idug.org]>mailto:[login to unmask email]>mailto:[login to unmask email]
> >Sent: Thursday, December 15, 2016 10:38 AM
> >To:
> <<mailto:[login to unmask email]>[login to unmask email]>mailto:[login to unmask email]>[login to unmask email]
> >Subject: [DB2-L] - Finding GDGs (in a column of DSNAMEs)
> >
> >I have z/OS filesystem DSNAMEs in a DB2 column.
> >Attempting to identify GDG levels in them, Iâ?™m using this:
> >
> >WITH
> >STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)
> >AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),
> > RIGHT(RTRIM(DSNAME),9)
> > FROM XXXXXX.TDSNAMES_WORKING
> > )
> >SELECT
> > CASE
> > WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
> > AND POSSTR(CANDIDATE_LEVEL,'V') = 7
> > AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'
> > AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'
> > AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'
> > AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'
> > AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'
> > AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'
> > THEN 'IS GDG LEVEL'
> > ELSE 'NOT GDG LEVEL'
> >END AS GDG_FLAG
> > ,CANDIDATE_BASE
> > ,CANDIDATE_LEVEL
> >FROM STAGE1;
> >
> >The CASE statement works, but Iâ?™m wondering if
> >thereâ?™s an easier way to identify a particular
> >string as numeric in SQL. Right now, Iâ?™m testing
> >each byte to see whether itâ?™s between 0 and 9.
> >This works, but seems like a kind of â?œthe hard wayâ? to do this.
> >
> >Does anyone know of a better/faster/less-SQL way to do this?
> >
> >--Phil Sevetson
> >x1688 (212-857-1688)
> >3rd Floor, Cube 223
> >FISA HRP Database Services
> >
> >
> >
> >Site Links:
> ><<http://www.idug.org/p/fo/st/post=179373&anc=p
> 179373#p179373>View>http://www.idug.org/p/fo/st/post=179373&anc=p179373#p179373>View
>
> >post
> >online
> ><<http://www.idug.org/p/fo/si/topic=19>View>htt
> p://www.idug.org/p/fo/si/topic=19>View
> >mailing list
> >online
> <<mailto:[login to unmask email]>Start>mailto:[login to unmask email]>Start new
> >thread via
> >email
> ><<mailto:[login to unmask email]?Subje
> ct=Unsubscribe>Unsubscribe>mailto:[login to unmask email]?Subject=Unsubscribe>Unsubscribe
>
> >from this mailing
> >list
> <http://www.idug.org/p/us/toManage>http://www.idug.org/p/us/toManage your
> >subscription This email has been sent to: [login to unmask email]
> >
> >** ** ** Attend the 2017 IDUG Tech Conference
> >North America ** ** ** ---> Anaheim, California,
> >April 30 - May 04, 2017 http://www.idug.org/na>http://www.idug.org/na
> >
> >
> >Use of this email content is governed by the terms of service at:
> ><<http://www.idug.org/p/cm/ld/fid=2>http://www.
> idug.org/p/cm/ld/fid=2>http://www.idug.org/p/cm/ld/fid=2>http://www.idug.org/p/cm/ld/fid=2
>
>
>Site Links:
><http://www.idug.org/p/fo/st/post=179387&anc=p179387#p179387>View
>post
>online
><http://www.idug.org/p/fo/si/topic=19>View
>mailing list
>online <mailto:[login to unmask email]>Start new
>thread via
>email
><mailto:[login to unmask email]?Subject=Unsubscribe>Unsubscribe
>from this mailing
>list http://www.idug.org/p/us/toManage your
>subscription This email has been sent to: [login to unmask email]
>
>** ** ** Attend the 2017 IDUG Tech Conference
>North America ** ** ** ---> Anaheim, California,
>April 30 - May 04, 2017 <--- <http://www.idug.org/na>http://www.idug.org/na
>
>
>Use of this email content is governed by the terms of service at:
><http://www.idug.org/p/cm/ld/fid=2>http://www.idug.org/p/cm/ld/fid=2

Walter Jani&#223;en

AW: Finding GDGs (in a column of DSNAMEs) -- blended solution
(in response to Lizette Koehler)
Hi Lizette

Some time ago there was a solution for that here in the list called REVERSE. I created this function in our company and it worked very well. Here is the source again (I don't know from whom this little piece came from, so sorry to not give him his credit):

CREATE FUNCTION DB2.REVERSE
(
INC VARCHAR(255) FOR SBCS DATA CCSID EBCDIC )
RETURNS VARCHAR(255) FOR SBCS DATA CCSID EBCDIC
LANGUAGE SQL
NO EXTERNAL ACTION
PARAMETER CCSID EBCDIC
DETERMINISTIC
NOT SECURED
CALLED ON NULL INPUT
CONTAINS SQL
SPECIFIC REVERSE
RETURN CASE WHEN INC = '' THEN CAST(INC AS VARCHAR(255)) ELSE
CAST(TRANSLATE(LEFT( X'FEFDFCFBFAF9F8F7F6F5F4F3F2F1F0'.. X'
EFEEEDECEBEAE9E8E7E6E5E4E3E2E1E0'.. X'DFDEDDDCDBDAD9D8D7D6D5D4D3D2D1D0'.
. X'CFCECDCCCBCAC9C8C7C6C5C4C3C2C1C0'.. X'BFBEBDBCBBBAB9B8B7B6B5B4B3B2B1
B0'.. X'AFAEADACABAAA9A8A7A6A5A4A3A2A1A0'.. X'9F9E9D9C9B9A99989796959493
929190'.. X'8F8E8D8C8B8A89888786858483828180'.. X'7F7E7D7C7B7A7978777675
7473727170'.. X'6F6E6D6C6B6A69686766656463626160'.. X'5F5E5D5C5B5A595857
56555453525150'.. X'4F4E4D4C4B4A49484746454443424140'.. X'3F3E3D3C3B3A39
383736353433323130'.. X'2F2E2D2C2B2A29282726252423222120'.. X'1F1E1D1C1B
1A19181716151413121110'.. X'0F0E0D0C0B0A09080706050403020100' ,LENGTH(IN
C)) ,INC ,RIGHT( X'000102030405060708090A0B0C0D0E0F'.. X'101112131415161
718191A1B1C1D1E1F'.. X'202122232425262728292A2B2C2D2E2F'.. X'30313233343
5363738393A3B3C3D3E3F'.. X'404142434445464748494A4B4C4D4E4F'.. X'5051525
35455565758595A5B5C5D5E5F'.. X'606162636465666768696A6B6C6D6E6F'.. X'707
172737475767778797A7B7C7D7E7F'.. X'808182838485868788898A8B8C8D8E8F'.. X
'909192939495969798999A9B9C9D9E9F'.. X'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF'
.. X'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF'.. X'C0C1C2C3C4C5C6C7C8C9CACBCCCDC
ECF'.. X'D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF'.. X'E0E1E2E3E4E5E6E7E8E9EAEBE
CEDEEEF'.. X'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFE' ,LENGTH(INC))) AS VARCHAR(2
55) ) END;

Maybe you are able to find it in the archive.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Lizette Koehler [mailto:[login to unmask email]
Gesendet: Donnerstag, 15. Dezember 2016 19:54
An: [login to unmask email]
Betreff: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution

So, I am not great with SQL, but I had a similar requirement in SAS. The Nodes varied to when you got to the GooooVoo number

The technique I used was to flip/revers the dataset name so A.B.C.GooooVoo was GooooVoo.c.b.a (or maybe it was ooVooooG.c.b.a) Then the first node could be extracted and checked to see if it was a GooooVoo number


Can something like that be done in SQL?

Lizette


From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:14 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs) -- blended solution

Lock, Sam,

Of course, after I published a reply, a better solution popped into my head:
CASE
WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
AND POSSTR(CANDIDATE_LEVEL,'V') = 7
AND TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4)
,'9999999999'
,'0123456789'
)
CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2)
,'9999999999'
,'0123456789'
)
='999999'
THEN 'IS GDG'
ELSE 'NOT GDG'
END AS GDG_FLAG

This mixes your stuff and mine and results in a precise solution with fewer function calls than my original. Thanks for your contributions :-)

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 12:05 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)

Lock, Sam,

I think I see a problem with that - call me a perfectionist. Also, a nit. The order of the _from-string_ and the _to-string_ needs to be reversed, according to the syntax diagram (P. 621, ch.4, "Built-in functions", topic TRANSLATE, in dsnsqm0d.pdf, the V10 SQL Reference)

1) If the period, G, and V are indiscriminately translated and some rat has put them elsewhere than they belong, this code won't find that error.

2) However, I can probably fix this with SUBSTR function to spike out the positions for ".G" and "V" --

So, this would look like:
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,1,1),'9','.') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,2,1),'9','G') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,3,4),'9999999999','0123456789') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,7,1),'9','V') CONCAT
TRANSLATE(SUBSTR(CANDIDATE_LEVEL,8,2),'9999999999','0123456789')
='999999999'

Which isn't as much of a savings as I'd hoped for, but is shorter and looks like it's using fewer operations (TRANSLATE and SUBSTR five times each, instead of two POSSTR, six SUBSTR, and six BETWEEN).

But even this is flawed for one case: if there's already a '9' in position 7, that's wrong for GDG levels and this code doesn't find it. (We know that there will not be a '9' in positions 1 or 2, unless the CANDIDATE_LEVEL isn't about a real GDG because the low level qualifier is too short; if so, the test fails when a period is found in some later byte of the column.)

--Phil


From: Lock Lyon [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:56 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Finding GDGs (in a column of DSNAMEs)

Phil,

Use TRANSLATE to convert chars 0-9, 'G', and 'V' to '9', then test the string for nines, like (*NotTested* !!):

AND TRANSLATE (SUBSTR(CANDIDATE_LEVEL,1,8),'GV0123456789', '999999999999') = '99999999'

- Lock Lyon

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 10:38 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Finding GDGs (in a column of DSNAMEs)

I have z/OS filesystem DSNAMEs in a DB2 column. Attempting to identify GDG levels in them, I'm using this:

WITH
STAGE1(CANDIDATE_BASE, CANDIDATE_LEVEL)
AS (SELECT LEFT(RTRIM(DSNAME),LENGTH(RTRIM(DSNAME))-9),
RIGHT(RTRIM(DSNAME),9)
FROM XXXXXX.TDSNAMES_WORKING
)
SELECT
CASE
WHEN POSSTR(CANDIDATE_LEVEL,'.G') = 1
AND POSSTR(CANDIDATE_LEVEL,'V') = 7
AND SUBSTR(CANDIDATE_LEVEL,3,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,4,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,5,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,6,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,8,1) BETWEEN '0' AND '9'
AND SUBSTR(CANDIDATE_LEVEL,9,1) BETWEEN '0' AND '9'
THEN 'IS GDG LEVEL'
ELSE 'NOT GDG LEVEL'
END AS GDG_FLAG
,CANDIDATE_BASE
,CANDIDATE_LEVEL
FROM STAGE1;

The CASE statement works, but I'm wondering if there's an easier way to identify a particular string as numeric in SQL. Right now, I'm testing each byte to see whether it's between 0 and 9. This works, but seems like a kind of "the hard way" to do this.

Does anyone know of a better/faster/less-SQL way to do this?

--Phil Sevetson
x1688 (212-857-1688)
3rd Floor, Cube 223
FISA HRP Database Services



-----End Original Message-----
Attachments

  • image001.png (2.6k)