DB2 z/OS Code Samples - any requests?

Todd Burch

DB2 z/OS Code Samples - any requests?
Hi All. Happy Friday.

I've gotten on a kick lately to post some code examples to the DB2 z/
OS Exchange out on Developerworks. I've posted a few things, and
instead of just guessing what might be interesting to people, I
figured I would ask.

So, you got any requests? I've quite an arsenal in my recreate bucket.

Have great weekend!!

Todd
DB2 z/OS Level 2

P.S. I am working on a Stored Procedure input/output variable trace
routine. It's coming along nice, but still a lot of code to go. I
mentioned this was doable in a thread a week or so ago when someone
was asking about such a feature. I think it will be pretty cool. So
far, I'm generating:

1) the DDL for the trace table (the table that will receive the values
of the input parms on entry and output parms on exit),
2) the shell of the CREATE FUNCTION DDL statements for the input SQL
Procedure and the output SQL Procedure,
3) the shell of the INSERT statements for the input and output parms
(which go into the above SQL Procedures).
4) the CALL statements that will go into the Stored Procedure that
needs the tracing

Not much more to do, then lots of testing with all data types. The
code is written in REXX, but will generate for proper syntax (for the
CALL statements) for whatever language you want. Should work on both
V8 and V9 just fine.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now open and free for all.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help 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 DB2-L

Michael Kalena

Re: DB2 z/OS Code Samples - any requests?
(in response to Todd Burch)
Funny you should ask.

Another DBA here was just experimenting with using DBINFO on a SP call
to get the thread's LUWID and pass it back to the caller. We're DB2 v8
on z/OS.

It didn't work as documented but looks like it's an undocumented parm?

i.e.

This Linkage Section:

LINKAGE SECTION.
*DECLARE THE SQLSTATE THAT CAN BE SET BY STORED PROC
01 P-SQLSTATE PIC X(5).
* DECLARE THE QUALIFIED PROCEDURE NAME
01 P-PROC.
49 P-PROC-LEN PIC 9(4) USAGE BINARY.
49 P-PROC-TEXT PIC X(27).
* DECLARE THE SPECIFIC PROCEDURE NAME
01 P-SPEC.
49 P-SPEC-LEN PIC 9(4) USAGE BINARY.
49 P-SPEC-TEXT PIC X(18).
* DECLARE SQL DIAGNOSTIC MESSAGE TOKEN
01 P-DIAG.
49 P-DIAG-LEN PIC 9(4) USAGE BINARY.
49 P-DIAG-TEXT PIC X(1000).
*********************************************************
*********************************************************
* STRUCTURE USED FOR DBINFO *
*********************************************************
01 SQLUDF-DBINFO.
* LOCATION NAME LENGTH
05 DBNAMELEN PIC 9(4) USAGE BINARY.
* LOCATION NAME
05 DBNAME PIC X(128).
* AUTHORIZATION ID LENGTH
05 AUTHIDLEN PIC 9(4) USAGE BINARY.
* AUTHORIZATION ID
05 AUTHID PIC X(128).
* ENVIRONMENT CCSID INFORMATION
05 CODEPG PIC X(48).
05 CDPG-DB2 REDEFINES CODEPG.
10 DB2-CCSIDS OCCURS 3 TIMES.
15 DB2-SBCS PIC 9(9) USAGE BINARY.
15 DB2-DBCS PIC 9(9) USAGE BINARY.
15 DB2-MIXED PIC 9(9) USAGE BINARY.
10 ENCODING-SCHEME PIC 9(9) USAGE BINARY.
10 RESERVED PIC X(8).
* SCHEMA NAME LENGTH
05 TBSCHEMALEN PIC 9(4) USAGE BINARY.
* SCHEMA NAME
05 TBSCHEMA PIC X(128).
* TABLE NAME LENGTH
05 TBNAMELEN PIC 9(4) USAGE BINARY.
* TABLE NAME
05 TBNAME PIC X(128).
* COLUMN NAME LENGTH
05 COLNAMELEN PIC 9(4) USAGE BINARY.
* COLUMN NAME
05 COLNAME PIC X(128).
* PRODUCT INFORMATION
05 VER-REL PIC X(8).
* RESERVED FOR EXPANSION
05 RESD0 PIC X(2).
* PLATFORM TYPE
05 PLATFORM PIC 9(9) USAGE BINARY.
* NUMBER OF ENTRIES IN TFCOLUMN LIST ARRAY (TFCOLUMN, BELOW)
05 NUMTFCOL PIC 9(4) USAGE BINARY.
* RESERVED FOR EXPANSION
05 RESD1 PIC X(26).
* TFCOLUMN WILL BE ALLOCATED DYNAMICALLY IF TF IS DEFINED
* OTHERWISE THIS WILL BE A NULL POINTER
05 TFCOLUMN USAGE IS POINTER.
* APPLICATION IDENTIFIER
05 APPL-ID USAGE IS POINTER.
* RESERVED FOR EXPANSION
05 RESD2 PIC X(20).

01 LK-APPL-ID PIC X(28).

*---------------------------------------------------------------*
PROCEDURE DIVISION USING P-SQLSTATE
,P-PROC
,P-SPEC
,P-DIAG
,SQLUDF-DBINFO
,LK-APPL-ID.
*---------------------------------------------------------------*

Where LK-APPL-ID is the extra parm, gives us what we wanted.

The doc seems to say APPL-ID is the address to the LUWID yet when we
look at what it points to, we get garbage.

If you could help us with this, that would be great.

Thanks.



Michael Kalena
973-793-2133
[login to unmask email]

DB2 Info Page at hBSC
(http://whsysops1.is.bear.com/db2/)

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Todd Burch
Sent: Friday, November 06, 2009 3:24 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS Code Samples - any requests?

Hi All. Happy Friday.

I've gotten on a kick lately to post some code examples to the DB2 z/
OS Exchange out on Developerworks. I've posted a few things, and
instead of just guessing what might be interesting to people, I
figured I would ask.

So, you got any requests? I've quite an arsenal in my recreate bucket.

Have great weekend!!

Todd
DB2 z/OS Level 2

P.S. I am working on a Stored Procedure input/output variable trace
routine. It's coming along nice, but still a lot of code to go. I
mentioned this was doable in a thread a week or so ago when someone
was asking about such a feature. I think it will be pretty cool. So
far, I'm generating:

1) the DDL for the trace table (the table that will receive the values
of the input parms on entry and output parms on exit),
2) the shell of the CREATE FUNCTION DDL statements for the input SQL
Procedure and the output SQL Procedure,
3) the shell of the INSERT statements for the input and output parms
(which go into the above SQL Procedures).
4) the CALL statements that will go into the Stored Procedure that
needs the tracing

Not much more to do, then lots of testing with all data types. The
code is written in REXX, but will generate for proper syntax (for the
CALL statements) for whatever language you want. Should work on both
V8 and V9 just fine.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now open and free for all.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help 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 DB2-L
This email is confidential and subject to important disclaimers and
conditions including on offers for the purchase or sale of
securities, accuracy and completeness of information, viruses,
confidentiality, legal privilege, and legal entity disclaimers,
available at http://www.jpmorgan.com/pages/disclosures/email.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now open and free for all.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help 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 DB2-L

Philip Sevetson

Re: DB2 z/OS Code Samples - any requests?
(in response to Michael Kalena)
I'm not sure if it'd be useful, or just cute... but I rewrote one of Suresh Sane's recursive SQLs a while ago, the one that generates a Fibonacci series. Would that be something you'd like to put out there for educational purposes?

--Phil

WITH FIB_SUM(LEVEL, PREV_NUM, NEW_NUM) AS
(SELECT 1,0,1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT LEVEL+1, NEW_NUM, PREV_NUM + NEW_NUM
FROM FIB_SUM
WHERE LEVEL<=10 )
SELECT A.LEVEL, A.NEW_NUM
FROM FIB_SUM A
ORDER BY 1
;


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Todd Burch
Sent: Friday, November 06, 2009 3:24 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS Code Samples - any requests?

Hi All. Happy Friday.

I've gotten on a kick lately to post some code examples to the DB2 z/
OS Exchange out on Developerworks. I've posted a few things, and
instead of just guessing what might be interesting to people, I
figured I would ask.

So, you got any requests? I've quite an arsenal in my recreate bucket.

Have great weekend!!

Todd
DB2 z/OS Level 2

P.S. I am working on a Stored Procedure input/output variable trace
routine. It's coming along nice, but still a lot of code to go. I
mentioned this was doable in a thread a week or so ago when someone
was asking about such a feature. I think it will be pretty cool. So
far, I'm generating:

1) the DDL for the trace table (the table that will receive the values
of the input parms on entry and output parms on exit),
2) the shell of the CREATE FUNCTION DDL statements for the input SQL
Procedure and the output SQL Procedure,
3) the shell of the INSERT statements for the input and output parms
(which go into the above SQL Procedures).
4) the CALL statements that will go into the Stored Procedure that
needs the tracing

Not much more to do, then lots of testing with all data types. The
code is written in REXX, but will generate for proper syntax (for the
CALL statements) for whatever language you want. Should work on both
V8 and V9 just fine.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now open and free for all.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help 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 DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now open and free for all.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help 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 DB2-L

Todd Burch

Re: DB2 z/OS Code Samples - any requests?
(in response to Philip Sevetson)
Hi Phil.

I would suggest that since this something that you've done, or Suresh
has done, that one of y'all post it out there.

Just create a logon id, filter for ALL, and click SUBMIT on the right
margin.

Here's the link for the DB2 z/OS stuff on Developerworks:
http://www.ibm.com/developerworks/data/products/db2zos/

Scroll down to the link for the DB2 Exchange.

(Note: the site is weird about cookies and the "remember my logon"
feature with IE. If you try to navigate to the Exchange and get a
blank page, please voice your frustration very loud and maybe they'll
fix it.)

Todd


On Nov 6, 2009, at 3:43 PM, Sevetson, Phil wrote:

I'm not sure if it'd be useful, or just cute... but I rewrote one of
Suresh Sane's recursive SQLs a while ago, the one that generates a
Fibonacci series. Would that be something you'd like to put out there
for educational purposes?

--Phil

WITH FIB_SUM(LEVEL, PREV_NUM, NEW_NUM) AS
(SELECT 1,0,1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT LEVEL+1, NEW_NUM, PREV_NUM + NEW_NUM
FROM FIB_SUM
WHERE LEVEL<=10 )
SELECT A.LEVEL, A.NEW_NUM
FROM FIB_SUM A
ORDER BY 1
;


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Todd Burch
Sent: Friday, November 06, 2009 3:24 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS Code Samples - any requests?

Hi All. Happy Friday.

I've gotten on a kick lately to post some code examples to the DB2 z/
OS Exchange out on Developerworks. I've posted a few things, and
instead of just guessing what might be interesting to people, I
figured I would ask.

So, you got any requests? I've quite an arsenal in my recreate bucket.

Have great weekend!!

Todd
DB2 z/OS Level 2

P.S. I am working on a Stored Procedure input/output variable trace
routine. It's coming along nice, but still a lot of code to go. I
mentioned this was doable in a thread a week or so ago when someone
was asking about such a feature. I think it will be pretty cool. So
far, I'm generating:

1) the DDL for the trace table (the table that will receive the values
of the input parms on entry and output parms on exit),
2) the shell of the CREATE FUNCTION DDL statements for the input SQL
Procedure and the output SQL Procedure,
3) the shell of the INSERT statements for the input and output parms
(which go into the above SQL Procedures).
4) the CALL statements that will go into the Stored Procedure that
needs the tracing

Not much more to do, then lots of testing with all data types. The
code is written in REXX, but will generate for proper syntax (for the
CALL statements) for whatever language you want. Should work on both
V8 and V9 just fine.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA
*
_____________________________________________________________________

http://www.IDUG.org membership is now open and free for all.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help 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 DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA
*
_____________________________________________________________________

http://www.IDUG.org membership is now open and free for all.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help 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 DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

Todd Burch

Re: DB2 z/OS Code Samples - any requests?
(in response to Todd Burch)
I'll look into this.

Todd


On Nov 6, 2009, at 3:20 PM, Kalena, Michael wrote:

Funny you should ask.

Another DBA here was just experimenting with using DBINFO on a SP call
to get the thread's LUWID and pass it back to the caller. We're DB2 v8
on z/OS.

It didn't work as documented but looks like it's an undocumented parm?

i.e.

This Linkage Section:

LINKAGE SECTION.
*DECLARE THE SQLSTATE THAT CAN BE SET BY STORED PROC
01 P-SQLSTATE PIC X(5).
* DECLARE THE QUALIFIED PROCEDURE NAME
01 P-PROC.
49 P-PROC-LEN PIC 9(4) USAGE BINARY.
49 P-PROC-TEXT PIC X(27).
* DECLARE THE SPECIFIC PROCEDURE NAME
01 P-SPEC.
49 P-SPEC-LEN PIC 9(4) USAGE BINARY.
49 P-SPEC-TEXT PIC X(18).
* DECLARE SQL DIAGNOSTIC MESSAGE TOKEN
01 P-DIAG.
49 P-DIAG-LEN PIC 9(4) USAGE BINARY.
49 P-DIAG-TEXT PIC X(1000).
*********************************************************
*********************************************************
* STRUCTURE USED FOR DBINFO *
*********************************************************
01 SQLUDF-DBINFO.
* LOCATION NAME LENGTH
05 DBNAMELEN PIC 9(4) USAGE BINARY.
* LOCATION NAME
05 DBNAME PIC X(128).
* AUTHORIZATION ID LENGTH
05 AUTHIDLEN PIC 9(4) USAGE BINARY.
* AUTHORIZATION ID
05 AUTHID PIC X(128).
* ENVIRONMENT CCSID INFORMATION
05 CODEPG PIC X(48).
05 CDPG-DB2 REDEFINES CODEPG.
10 DB2-CCSIDS OCCURS 3 TIMES.
15 DB2-SBCS PIC 9(9) USAGE BINARY.
15 DB2-DBCS PIC 9(9) USAGE BINARY.
15 DB2-MIXED PIC 9(9) USAGE BINARY.
10 ENCODING-SCHEME PIC 9(9) USAGE BINARY.
10 RESERVED PIC X(8).
* SCHEMA NAME LENGTH
05 TBSCHEMALEN PIC 9(4) USAGE BINARY.
* SCHEMA NAME
05 TBSCHEMA PIC X(128).
* TABLE NAME LENGTH
05 TBNAMELEN PIC 9(4) USAGE BINARY.
* TABLE NAME
05 TBNAME PIC X(128).
* COLUMN NAME LENGTH
05 COLNAMELEN PIC 9(4) USAGE BINARY.
* COLUMN NAME
05 COLNAME PIC X(128).
* PRODUCT INFORMATION
05 VER-REL PIC X(8).
* RESERVED FOR EXPANSION
05 RESD0 PIC X(2).
* PLATFORM TYPE
05 PLATFORM PIC 9(9) USAGE BINARY.
* NUMBER OF ENTRIES IN TFCOLUMN LIST ARRAY (TFCOLUMN, BELOW)
05 NUMTFCOL PIC 9(4) USAGE BINARY.
* RESERVED FOR EXPANSION
05 RESD1 PIC X(26).
* TFCOLUMN WILL BE ALLOCATED DYNAMICALLY IF TF IS DEFINED
* OTHERWISE THIS WILL BE A NULL POINTER
05 TFCOLUMN USAGE IS POINTER.
* APPLICATION IDENTIFIER
05 APPL-ID USAGE IS POINTER.
* RESERVED FOR EXPANSION
05 RESD2 PIC X(20).

01 LK-APPL-ID PIC X(28).

*---------------------------------------------------------------*
PROCEDURE DIVISION USING P-SQLSTATE
,P-PROC
,P-SPEC
,P-DIAG
,SQLUDF-DBINFO
,LK-APPL-ID.
*---------------------------------------------------------------*

Where LK-APPL-ID is the extra parm, gives us what we wanted.

The doc seems to say APPL-ID is the address to the LUWID yet when we
look at what it points to, we get garbage.

If you could help us with this, that would be great.

Thanks.



Michael Kalena

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

Bernd Oppolzer

Re: DB2 z/OS Code Samples - any requests?
(in response to Todd Burch)
I did that in PL/1 recently, and it worked. It's not undocumented.

I think the key is that in your example you need to identify the
LK_APPL_ID definition with the pointer in the DBINFO structure.
I don't know how this has to be done in COBOL, but it can be done.
The APPL_ID is not passed as an extra pointer via USING in the
PROCEDURE DIVISION, that is not the right way. Ask your
local COBOL techies.

Kind regards

Bernd



Kalena, Michael schrieb:
> Funny you should ask.
>
> Another DBA here was just experimenting with using DBINFO on a SP call
> to get the thread's LUWID and pass it back to the caller. We're DB2 v8
> on z/OS.
>
> It didn't work as documented but looks like it's an undocumented parm?
>
> i.e.
>
> This Linkage Section:
>
> LINKAGE SECTION.
> *DECLARE THE SQLSTATE THAT CAN BE SET BY STORED PROC
> 01 P-SQLSTATE PIC X(5).
> * DECLARE THE QUALIFIED PROCEDURE NAME
> 01 P-PROC.
> 49 P-PROC-LEN PIC 9(4) USAGE BINARY.
> 49 P-PROC-TEXT PIC X(27).
> * DECLARE THE SPECIFIC PROCEDURE NAME
> 01 P-SPEC.
> 49 P-SPEC-LEN PIC 9(4) USAGE BINARY.
> 49 P-SPEC-TEXT PIC X(18).
> * DECLARE SQL DIAGNOSTIC MESSAGE TOKEN
> 01 P-DIAG.
> 49 P-DIAG-LEN PIC 9(4) USAGE BINARY.
> 49 P-DIAG-TEXT PIC X(1000).
> *********************************************************
> *********************************************************
> * STRUCTURE USED FOR DBINFO *
> *********************************************************
> 01 SQLUDF-DBINFO.
> * LOCATION NAME LENGTH
> 05 DBNAMELEN PIC 9(4) USAGE BINARY.
> * LOCATION NAME
> 05 DBNAME PIC X(128).
> * AUTHORIZATION ID LENGTH
> 05 AUTHIDLEN PIC 9(4) USAGE BINARY.
> * AUTHORIZATION ID
> 05 AUTHID PIC X(128).
> * ENVIRONMENT CCSID INFORMATION
> 05 CODEPG PIC X(48).
> 05 CDPG-DB2 REDEFINES CODEPG.
> 10 DB2-CCSIDS OCCURS 3 TIMES.
> 15 DB2-SBCS PIC 9(9) USAGE BINARY.
> 15 DB2-DBCS PIC 9(9) USAGE BINARY.
> 15 DB2-MIXED PIC 9(9) USAGE BINARY.
> 10 ENCODING-SCHEME PIC 9(9) USAGE BINARY.
> 10 RESERVED PIC X(8).
> * SCHEMA NAME LENGTH
> 05 TBSCHEMALEN PIC 9(4) USAGE BINARY.
> * SCHEMA NAME
> 05 TBSCHEMA PIC X(128).
> * TABLE NAME LENGTH
> 05 TBNAMELEN PIC 9(4) USAGE BINARY.
> * TABLE NAME
> 05 TBNAME PIC X(128).
> * COLUMN NAME LENGTH
> 05 COLNAMELEN PIC 9(4) USAGE BINARY.
> * COLUMN NAME
> 05 COLNAME PIC X(128).
> * PRODUCT INFORMATION
> 05 VER-REL PIC X(8).
> * RESERVED FOR EXPANSION
> 05 RESD0 PIC X(2).
> * PLATFORM TYPE
> 05 PLATFORM PIC 9(9) USAGE BINARY.
> * NUMBER OF ENTRIES IN TFCOLUMN LIST ARRAY (TFCOLUMN, BELOW)
> 05 NUMTFCOL PIC 9(4) USAGE BINARY.
> * RESERVED FOR EXPANSION
> 05 RESD1 PIC X(26).
> * TFCOLUMN WILL BE ALLOCATED DYNAMICALLY IF TF IS DEFINED
> * OTHERWISE THIS WILL BE A NULL POINTER
> 05 TFCOLUMN USAGE IS POINTER.
> * APPLICATION IDENTIFIER
> 05 APPL-ID USAGE IS POINTER.
> * RESERVED FOR EXPANSION
> 05 RESD2 PIC X(20).
>
> 01 LK-APPL-ID PIC X(28).
>
> *---------------------------------------------------------------*
> PROCEDURE DIVISION USING P-SQLSTATE
> ,P-PROC
> ,P-SPEC
> ,P-DIAG
> ,SQLUDF-DBINFO
> ,LK-APPL-ID.
> *---------------------------------------------------------------*
>
> Where LK-APPL-ID is the extra parm, gives us what we wanted.
>
> The doc seems to say APPL-ID is the address to the LUWID yet when we
> look at what it points to, we get garbage.
>
> If you could help us with this, that would be great.
>
> Thanks.
>
>
>
> Michael Kalena
> 973-793-2133
> [login to unmask email]
>
> DB2 Info Page at hBSC
> (http://whsysops1.is.bear.com/db2/)
>
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

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

Avram Friedman

Re: DB2 z/OS Code Samples - any requests?
(in response to Bernd Oppolzer)
Seems a bit odd to me.
This is the IDUG DB2-L listserv
and
the IDUG DB2 CODEPAGE is not under consideration for a place to post
code samples.

I have no complaints with other locations for DB2 help but one should be
aware of where they are at.

Regards
Avram Friedman

On Fri, 6 Nov 2009 14:23:40 -0600, Todd Burch <[login to unmask email]>
wrote:

>Hi All. Happy Friday.
>
>I've gotten on a kick lately to post some code examples to the DB2 z/
>OS Exchange out on Developerworks. I've posted a few things, and
>instead of just guessing what might be interesting to people, I
>figured I would ask.
>
>So, you got any requests? I've quite an arsenal in my recreate bucket.
>
>Have great weekend!!
>
>Todd
>DB2 z/OS Level 2
>
>P.S. I am working on a Stored Procedure input/output variable trace
>routine. It's coming along nice, but still a lot of code to go. I
>mentioned this was doable in a thread a week or so ago when someone
>was asking about such a feature. I think it will be pretty cool. So
>far, I'm generating:
>
>1) the DDL for the trace table (the table that will receive the values
>of the input parms on entry and output parms on exit),
>2) the shell of the CREATE FUNCTION DDL statements for the input SQL
>Procedure and the output SQL Procedure,
>3) the shell of the INSERT statements for the input and output parms
>(which go into the above SQL Procedures).
>4) the CALL statements that will go into the Stored Procedure that
>needs the tracing
>
>Not much more to do, then lots of testing with all data types. The
>code is written in REXX, but will generate for proper syntax (for the
>CALL statements) for whatever language you want. Should work on both
>V8 and V9 just fine.
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.IDUG.org membership is now open and free for all.
>Do you have people in your office who are not an IDUG member?
>Show them how to access the information and help 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 DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

Philip Sevetson

Re: DB2 z/OS Code Samples - any requests?
(in response to Avram Friedman)
Done.

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Todd Burch
Sent: Friday, November 06, 2009 11:22 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS Code Samples - any requests?

Hi Phil.

I would suggest that since this something that you've done, or Suresh
has done, that one of y'all post it out there.

Just create a logon id, filter for ALL, and click SUBMIT on the right
margin.

Here's the link for the DB2 z/OS stuff on Developerworks:
http://www.ibm.com/developerworks/data/products/db2zos/

Scroll down to the link for the DB2 Exchange.

(Note: the site is weird about cookies and the "remember my logon"
feature with IE. If you try to navigate to the Exchange and get a
blank page, please voice your frustration very loud and maybe they'll
fix it.)

Todd


On Nov 6, 2009, at 3:43 PM, Sevetson, Phil wrote:

I'm not sure if it'd be useful, or just cute... but I rewrote one of
Suresh Sane's recursive SQLs a while ago, the one that generates a
Fibonacci series. Would that be something you'd like to put out there
for educational purposes?

--Phil

WITH FIB_SUM(LEVEL, PREV_NUM, NEW_NUM) AS
(SELECT 1,0,1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT LEVEL+1, NEW_NUM, PREV_NUM + NEW_NUM
FROM FIB_SUM
WHERE LEVEL<=10 )
SELECT A.LEVEL, A.NEW_NUM
FROM FIB_SUM A
ORDER BY 1
;


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Todd Burch
Sent: Friday, November 06, 2009 3:24 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS Code Samples - any requests?

Hi All. Happy Friday.

I've gotten on a kick lately to post some code examples to the DB2 z/
OS Exchange out on Developerworks. I've posted a few things, and
instead of just guessing what might be interesting to people, I
figured I would ask.

So, you got any requests? I've quite an arsenal in my recreate bucket.

Have great weekend!!

Todd
DB2 z/OS Level 2

P.S. I am working on a Stored Procedure input/output variable trace
routine. It's coming along nice, but still a lot of code to go. I
mentioned this was doable in a thread a week or so ago when someone
was asking about such a feature. I think it will be pretty cool. So
far, I'm generating:

1) the DDL for the trace table (the table that will receive the values
of the input parms on entry and output parms on exit),
2) the shell of the CREATE FUNCTION DDL statements for the input SQL
Procedure and the output SQL Procedure,
3) the shell of the INSERT statements for the input and output parms
(which go into the above SQL Procedures).
4) the CALL statements that will go into the Stored Procedure that
needs the tracing

Not much more to do, then lots of testing with all data types. The
code is written in REXX, but will generate for proper syntax (for the
CALL statements) for whatever language you want. Should work on both
V8 and V9 just fine.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA
*
_____________________________________________________________________

http://www.IDUG.org membership is now open and free for all.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help 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 DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA
*
_____________________________________________________________________

http://www.IDUG.org membership is now open and free for all.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help 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 DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

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

Todd Burch

Re: DB2 z/OS Code Samples - any requests?
(in response to Philip Sevetson)
Point taken Avram, but being an IBMer, and in following the processes
that must be followed to post code for public consumption,
Developerworks is what works for me. If the IDUG list admins do not
want these posts, I will certainly comply and stop.

With that said, I have posted some more free code.

This time it's a DBRM-Release-Marker-Finder UDF (user defined
function), which allows you to obtain the release marker of a DBRM via
SQL instead of running a home grown (rexx-type) solution, or manually
inspecting DBRMs, if you ever get into a pinch and have a need to know
what DB2 version that hundreds of DBRMs were created with.

You can download it from Developerworks:

http://www.ibm.com/developerworks/exchange/dw_entryView.jspa?externalID=1211&categoryID=50

This is not something to run willy-nilly for fun on a production
system. It uses a lot of resources for every value passed to it. (ICF
catalog lookups, dynamic allocation and freeing, OPENing, GETing and
CLOSEing of the DBRM, plus all the memory allocation, error checking
and recovery necessary for such tasks. A query on my dinky system that
returns a few dozen rows takes every bit of 3-4 seconds, and can
produce WLM job log output when errors occur, like when PDS libraries
or members do not exist. (S013-18 abends, etc - all recoverable though
and transparent to the query).

There's a readme file included, plus sample SQL and output, along with
all the source code (assembler). It's even been set up to run in
debug mode with WTO's showing progress and indicators for why you
might get the all encompassing return value of '?' (release marker
cannot be determined) instead of a valid release marker when there are
errors.

It can be used to find missing DBRM PDS libraries and/or missing and
invalid DBRM members too.

I'm still working on the Stored Procedure Parm debugger. It's 80%
there.

Enjoy. Todd





On Nov 8, 2009, at 9:36 PM, Avram Friedman wrote:

Seems a bit odd to me.
This is the IDUG DB2-L listserv
and
the IDUG DB2 CODEPAGE is not under consideration for a place to post
code samples.

I have no complaints with other locations for DB2 help but one should be
aware of where they are at.

Regards
Avram Friedman

On Fri, 6 Nov 2009 14:23:40 -0600, Todd Burch <[login to unmask email]>
wrote:

> Hi All. Happy Friday.
>
> I've gotten on a kick lately to post some code examples to the DB2 z/
> OS Exchange out on Developerworks. I've posted a few things, and
> instead of just guessing what might be interesting to people, I
> figured I would ask.
>
> So, you got any requests? I've quite an arsenal in my recreate
> bucket.
>
> Have great weekend!!
>
> Todd
> DB2 z/OS Level 2
>
> P.S. I am working on a Stored Procedure input/output variable trace
> routine. It's coming along nice, but still a lot of code to go. I
> mentioned this was doable in a thread a week or so ago when someone
> was asking about such a feature. I think it will be pretty cool. So
> far, I'm generating:
>
> 1) the DDL for the trace table (the table that will receive the values
> of the input parms on entry and output parms on exit),
> 2) the shell of the CREATE FUNCTION DDL statements for the input SQL
> Procedure and the output SQL Procedure,
> 3) the shell of the INSERT statements for the input and output parms
> (which go into the above SQL Procedures).
> 4) the CALL statements that will go into the Stored Procedure that
> needs the tracing
>
> Not much more to do, then lots of testing with all data types. The
> code is written in REXX, but will generate for proper syntax (for the
> CALL statements) for whatever language you want. Should work on both
> V8 and V9 just fine.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help 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 DB2-L