Stored Procedure to read flat files

jeffrey agosta

Stored Procedure to read flat files

Hi - 

I'm looking for a quick and dirty example of a stored procedure (REXX or Native ?) that will read from a nonDBMS source, in this case a flat file. If anyone might have something to share, I'd greatly appreciate it.

Tku,

Jeff A

Lance Jackson

Stored Procedure to read flat files
(in response to jeffrey agosta)
Jeff,


On which platform do you intend to run the REXX program? z/OS or Windows? The file-handling approach differs for each.


Lance J.
-----Original Message-----
From: jeffrey agosta [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 08:43 AM
To: [login to unmask email]
Subject: [DB2-L] - Stored Procedure to read flat files

Hi -
I'm looking for a quick and dirty example of a stored procedure (REXX or Native ?) that will read from a nonDBMS source, in this case a flat file. If anyone might have something to share, I'd greatly appreciate it.
Tku,
Jeff A


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

Philip Sevetson

Stored Procedure to read flat files
(in response to Lance Jackson)
**please note my email address change**
Lance, I’m going to jump in here and say that, even if Jeff doesn’t want to know about the z/OS side, I do.

I’d like to put it (both of them) in the Code Place if there’s a solution, and would be happy to credit you if you know what it is.

From: Lance D. Jackson [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 9:48 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Stored Procedure to read flat files

Jeff,

On which platform do you intend to run the REXX program? z/OS or Windows? The file-handling approach differs for each.

Lance J.
-----Original Message-----
From: jeffrey agosta [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 08:43 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Stored Procedure to read flat files

Hi -

I'm looking for a quick and dirty example of a stored procedure (REXX or Native ?) that will read from a nonDBMS source, in this case a flat file. If anyone might have something to share, I'd greatly appreciate it.

Tku,

Jeff A

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

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Lance Jackson

Stored Procedure to read flat files
(in response to jeffrey agosta)
Will do Phil. Can you give me the URL for Code Place? Thanks.
-----Original Message-----
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 09:50 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Stored Procedure to read flat files

**please note my email address change**Lance, I’m going to jump in here and say that, even if Jeff doesn’t want to know about the z/OS side, I do.

I’d like to put it (both of them) in the Code Place if there’s a solution, and would be happy to credit you if you know what it is.

From: Lance D. Jackson [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 9:48 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Stored Procedure to read flat files

Jeff,



On which platform do you intend to run the REXX program? z/OS or Windows? The file-handling approach differs for each.



Lance J.

-----Original Message-----
From: jeffrey agosta [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 08:43 AM
To:[login to unmask email]
Subject: [DB2-L] - Stored Procedure to read flat files
Hi -
I'm looking for a quick and dirty example of a stored procedure (REXX or Native ?) that will read from a nonDBMS source, in this case a flat file. If anyone might have something to share, I'd greatly appreciate it.
Tku,
Jeff A

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

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



**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

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

jeffrey agosta

RE: Stored Procedure to read flat files
(in response to Lance Jackson)

Lance - 

Good question... I AM getting old and forgetful (a hit of the environment :-)

I'm a DBA on z/OS DB2 10 (now) - 11 (soon)... yeah I now we are way out of support :-(  I've built many excel front-ends which access DB2 via ODBC (both via SQL and Native SPs). What I'm looking to do is also access nonDBMS data to provide greater visibility into other related areas. For instance, we have a PerformanceDB which houses SMF 100/101's and a historical RTS DB. I'd like to add some pertinent system related info that provides more insight. That data is currently in flat files.

Tku,

Jeff A

BTW if anyone is interested, I am willing to share anything that might be of benefit     

In Reply to Lance Jackson:

Jeff,


On which platform do you intend to run the REXX program? z/OS or Windows? The file-handling approach differs for each.


Lance J.
-----Original Message-----
From: jeffrey agosta [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 08:43 AM
To: [login to unmask email]
Subject: [DB2-L] - Stored Procedure to read flat files

Hi -
I'm looking for a quick and dirty example of a stored procedure (REXX or Native ?) that will read from a nonDBMS source, in this case a flat file. If anyone might have something to share, I'd greatly appreciate it.
Tku,
Jeff A


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

Philip Sevetson

Stored Procedure to read flat files
(in response to Lance Jackson)
**please note my email address change**
Lance,

To get to the CodePlace, you log in to the IDUG website (membership required, and free, and if you’re posting here you’re already a member). From there, you can paste this:
http://www.idug.org/page/codeplace-home

and click on the link (“To submit your own code to share on Codeplace, click here http://www.idug.org/p/do/ed/topic=97 .”) to get a new empty record for that forum.

From: Lance D. Jackson [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 10:04 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Stored Procedure to read flat files

Will do Phil. Can you give me the URL for Code Place? Thanks.
-----Original Message-----
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 09:50 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Stored Procedure to read flat files
**please note my email address change**
Lance, I’m going to jump in here and say that, even if Jeff doesn’t want to know about the z/OS side, I do.

I’d like to put it (both of them) in the Code Place if there’s a solution, and would be happy to credit you if you know what it is.

From: Lance D. Jackson [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 9:48 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Stored Procedure to read flat files

Jeff,

On which platform do you intend to run the REXX program? z/OS or Windows? The file-handling approach differs for each.

Lance J.
-----Original Message-----
From: jeffrey agosta [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 08:43 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Stored Procedure to read flat files

Hi -

I'm looking for a quick and dirty example of a stored procedure (REXX or Native ?) that will read from a nonDBMS source, in this case a flat file. If anyone might have something to share, I'd greatly appreciate it.

Tku,

Jeff A

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

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----


-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

J&#248;rn Thyssen

RE: Stored Procedure to read flat files
(in response to jeffrey agosta)

Hi Jeffrey,

On Db2 for z/OS you can use Db2 supplied stored procedure ADMIN_DS_BROWSE

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sp_admindsbrowse.html

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Philip Sevetson

Stored Procedure to read flat files
(in response to Philip Sevetson)
And, note. My text in previous post (reproduced below) contains a copy of the submit link.

Don’t use my link, use the one on the IDUG webpage – I’m pretty sure reusing mine will cause a duplicate key error in the forum database.

--Phil

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 10:11 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Stored Procedure to read flat files

**please note my email address change**
Lance,

To get to the CodePlace, you log in to the IDUG website (membership required, and free, and if you’re posting here you’re already a member). From there, you can paste this:
http://www.idug.org/page/codeplace-home

and click on the link (“To submit your own code to share on Codeplace, click here http://www.idug.org/p/do/ed/topic=97 .”) to get a new empty record for that forum.

From: Lance D. Jackson [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 10:04 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Stored Procedure to read flat files

Will do Phil. Can you give me the URL for Code Place? Thanks.
-----Original Message-----
From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 09:50 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: Stored Procedure to read flat files
**please note my email address change**
Lance, I’m going to jump in here and say that, even if Jeff doesn’t want to know about the z/OS side, I do.

I’d like to put it (both of them) in the Code Place if there’s a solution, and would be happy to credit you if you know what it is.

From: Lance D. Jackson [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 9:48 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Stored Procedure to read flat files

Jeff,

On which platform do you intend to run the REXX program? z/OS or Windows? The file-handling approach differs for each.

Lance J.
-----Original Message-----
From: jeffrey agosta [mailto:[login to unmask email]
Sent: Thursday, November 30, 2017 08:43 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Stored Procedure to read flat files

Hi -

I'm looking for a quick and dirty example of a stored procedure (REXX or Native ?) that will read from a nonDBMS source, in this case a flat file. If anyone might have something to share, I'd greatly appreciate it.

Tku,

Jeff A

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

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----


-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

J&#248;rn Thyssen

[AD] RE: Stored Procedure to read flat files
(in response to jeffrey agosta)

Also:

IBM Data Virtualization Manager for z/OS (IDVM) can expose many data sources (including flat files) as a tables, so you can run SQL against it. IDVM would allow you to both read and update flat files through SQL. 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

jeffrey agosta

RE: Stored Procedure to read flat files
(in response to Jørn Thyssen)

Jørn -

Thanks will look into that.

Tku,

Jeff A


In Reply to Jørn Thyssen:

Hi Jeffrey,

On Db2 for z/OS you can use Db2 supplied stored procedure ADMIN_DS_BROWSE

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sp_admindsbrowse.html

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

jeffrey agosta

RE: Stored Procedure to read flat files
(in response to jeffrey agosta)

Jørn - 

Sadly I am not able to squeeze another nickle for vended software and will be developing in-house.

I did try the SP you recommended, SYSPROC.ADMIN_DS_BROWSE got it to work fine with a PDS returning data to excel. The flat file access, gave a valid return, but no data. may be an environmental issue.  

Tku,

Jeff A

In Reply to jeffrey agosta:

Jørn -

Thanks will look into that.

Tku,

Jeff A


In Reply to Jørn Thyssen:

Hi Jeffrey,

On Db2 for z/OS you can use Db2 supplied stored procedure ADMIN_DS_BROWSE

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sp_admindsbrowse.html

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

John Kliewe

RE: Stored Procedure to read flat files
(in response to jeffrey agosta)

Does anyone have an example of a trigger that calls ADMIN_DS_BROWSE?  I am in Db2 V11 for z/OS

My requirement is that a user is allowed to insert into table T1 only if they have READ access to a certain dataset.  The dataset access is controlled by RACF, and I thought it would be a good way to verify the user's access by attempting to read the dataset with ADMIN_DS_BROWSE. 

Been working on this for a few days now with no luck.  I've tried creating another stored proc, which itself calls ADMIN_DS_BROWSE and returns the results to the trigger.   But here again I am not having any luck.  I get this error :

DSNT408I SQLCODE = -438, SQLSTATE = 70001, UNKNOWN SQLSTATE TYPE FROM
SQL ROUTINE TOKENS DSNA618I DSNADMDB SQL ERROR DURING SQL
STATEMENT DELETE, TABLE=SYSIBM

--John K

Edited By:
John Kliewe[Organization Members] @ Jan 21, 2018 - 03:21 PM (America/Eastern)

Marcus Davage

Stored Procedure to read flat files
(in response to John Kliewe)
Classification: Public

Can you post your code or how you invoke the SP?

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator
LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG http://www.idug.org/p/us/sn/uid=24500 | Twitter http://twitter.com/spufidoo | About.me http://about.me/spufidoo
From: John Kliewe [mailto:[login to unmask email]
Sent: 21 January 2018 20:19
To: [login to unmask email]
Subject: [DB2-L] - RE: Stored Procedure to read flat files

-- This email has reached the Bank via an external source --


Does anyone have an example of a trigger that calls ADMIN_DS_BROWSE?

My requirement is that a user is allowed to insert into table T1 only if they have READ access to a certain dataset. The dataset access is controlled by RACF, and I thought it would be a good way to verify the user's access by attempting to read the dataset with ADMIN_DS_BROWSE.

Been working on this for a few days now with no luck. I've tried creating another stored proc, which itself calls ADMIN_DS_BROWSE and returns the results to the trigger. But here again I am not having any luck. I get this error :

DSNT408I SQLCODE = -438, SQLSTATE = 70001, UNKNOWN SQLSTATE TYPE FROM
SQL ROUTINE TOKENS DSNA618I DSNADMDB SQL ERROR DURING SQL
STATEMENT DELETE, TABLE=SYSIBM

--John K

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


Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555.

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.

John Kliewe

RE: Stored Procedure to read flat files
(in response to Marcus Davage)

Thanks Marcus,  

Sure - here are some more details :

I need a trigger that will prevent a user from inserting a value into table.column T1.OWNER unless they have READ access to a dataset named ICFSSP00.owner

For example, if I try to insert a row with MRMAGOO in the owner column, the trigger will check to be sure I have READ access to dataset ICFSSP00.MRMAGOO

First I assume that I cannot call ADMIN_DS_BROWSE directly from a trigger, because how do I set the inout variables? So I created a stored proc that looks like this :

CREATE PROCEDURE ICFSSP00.CHK_OWNER
(INOUT OWNER CHAR( 16) FOR SBCS DATA CCSID EBCDIC)
VERSION V1
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
DYNAMIC RESULT SETS 0
DISALLOW DEBUG MODE
PARAMETER CCSID EBCDIC
QUALIFIER ICFSSP00
PACKAGE OWNER ICFSSP00
ASUTIME NO LIMIT
COMMIT ON RETURN NO
INHERIT SPECIAL REGISTERS
WLM ENVIRONMENT FOR DEBUG MODE DSNDWLM
NODEFER PREPARE
CURRENT DATA NO
DEGREE 1
DYNAMICRULES RUN
APPLICATION ENCODING SCHEME EBCDIC
WITHOUT EXPLAIN
WITHOUT IMMEDIATE WRITE
ISOLATION LEVEL CS
WITHOUT KEEP DYNAMIC
OPTHINT ''
RELEASE AT COMMIT
REOPT NONE
VALIDATE RUN
ROUNDING DEC_ROUND_HALF_EVEN
DECIMAL(15)
BUSINESS_TIME SENSITIVE YES
SYSTEM_TIME SENSITIVE YES
ARCHIVE SENSITIVE YES
BEGIN
DECLARE DSNAME CHAR( 44) ;
DECLARE MBRNAME CHAR( 8) ;
DECLARE DUMPOPT CHAR( 1) ;
DECLARE I, J INTEGER ;
DECLARE M VARCHAR( 1331) ;
SET J = 1 ;
SET MBRNAME = ' ' ;
SET DUMPOPT = ' ' ;
IF SUBSTR(OWNER, 1, 8) <> OWNER THEN
SIGNAL SQLSTATE '70002' SET MESSAGE_TEXT = OWNER ||
' INVALID OWNER' ;
END IF ;
SET DSNAME = '''ICFSSP00.' || STRIP(OWNER) || '''' ;
CALL "SYSPROC".ADMIN_DS_BROWSE(J, DSNAME, MBRNAME, DUMPOPT, I, M)
;
IF I <> 0 THEN
SIGNAL SQLSTATE '70002' SET MESSAGE_TEXT = M ;
END IF ;
END `


and then my trigger looks like this :

--#SET TERMINATOR `
SET CURRENT SQLID='ICFSSP00'`
`
CREATE TRIGGER ICFSSP00.REQT02_BI02
NO CASCADE BEFORE INSERT ON REQT02
REFERENCING NEW AS NEWT02
FOR EACH ROW MODE DB2SQL
CALL ICFSSP00.CHK_OWNER(NEWT02.OWNER) `
--#SET TERMINATOR ;
COMMIT;

Because the owner must be an 8-character dataset qualifier, I return sqlstate 71002 if the owner is not an 8-char field.
At run time, I consistently get sqlstate 71001 returned, with a message indicating that something has failed during a DELETE from a SYSIBM table.
I assume this is the result table, which actually I don't expect to read.

James Campbell

Stored Procedure to read flat files
(in response to John Kliewe)
Have you been revoking insert/delete/update/select authority from created global temporary
tables with a SYSIBM creator id?

Since the data in temporary tables is unique to the thread that is using them, there is little
point, and a possible downside, in revoking this access.

SYSIBM.TEXT_REC_OUTPUT and SYSIBM.BIN_REC_OUTPUT.

James Campbell

On 22 Jan 2018 at 11:44, John Kliewe wrote:

>
> Thanks Marcus,  
> Sure - here are some more details :
> I need a trigger that will prevent a user from inserting a value into table.column T1.OWNER
> unless they have READ access to a dataset named ICFSSP00.owner
> For example, if I try to insert a row with MRMAGOO in the owner column, the trigger will check to
> be sure I have READ access to dataset ICFSSP00.MRMAGOO
> First I assume that I cannot call ADMIN_DS_BROWSE directly from a trigger, because how do I
> set the inout variables? So I created a stored proc that looks like this :
> CREATE PROCEDURE ICFSSP00.CHK_OWNER
> (INOUT OWNER CHAR( 16) FOR SBCS DATA CCSID EBCDIC)
> VERSION V1
> LANGUAGE SQL
> NOT DETERMINISTIC
> MODIFIES SQL DATA
> CALLED ON NULL INPUT
> DYNAMIC RESULT SETS 0
> DISALLOW DEBUG MODE
> PARAMETER CCSID EBCDIC
> QUALIFIER ICFSSP00
> PACKAGE OWNER ICFSSP00
> ASUTIME NO LIMIT
> COMMIT ON RETURN NO
> INHERIT SPECIAL REGISTERS
> WLM ENVIRONMENT FOR DEBUG MODE DSNDWLM
> NODEFER PREPARE
> CURRENT DATA NO
> DEGREE 1
> DYNAMICRULES RUN
> APPLICATION ENCODING SCHEME EBCDIC
> WITHOUT EXPLAIN
> WITHOUT IMMEDIATE WRITE
> ISOLATION LEVEL CS
> WITHOUT KEEP DYNAMIC
> OPTHINT ''
> RELEASE AT COMMIT
> REOPT NONE
> VALIDATE RUN
> ROUNDING DEC_ROUND_HALF_EVEN
> DECIMAL(15)
> BUSINESS_TIME SENSITIVE YES
> SYSTEM_TIME SENSITIVE YES
> ARCHIVE SENSITIVE YES
> BEGIN
> DECLARE DSNAME CHAR( 44) ;
> DECLARE MBRNAME CHAR( 8) ;
> DECLARE DUMPOPT CHAR( 1) ;
> DECLARE I, J INTEGER ;
> DECLARE M VARCHAR( 1331) ;
> SET J = 1 ;
> SET MBRNAME = ' ' ;
> SET DUMPOPT = ' ' ;
> IF SUBSTR(OWNER, 1, 8) <> OWNER THEN
> SIGNAL SQLSTATE '70002' SET MESSAGE_TEXT = OWNER ||
> ' INVALID OWNER' ;
> END IF ;
> SET DSNAME = '''ICFSSP00.' || STRIP(OWNER) || '''' ;
> CALL "SYSPROC".ADMIN_DS_BROWSE(J, DSNAME, MBRNAME, DUMPOPT, I, M)
> ;
> IF I <> 0 THEN
> SIGNAL SQLSTATE '70002' SET MESSAGE_TEXT = M ;
> END IF ;
> END `
>
> and then my trigger looks like this :
> --#SET TERMINATOR `
> SET CURRENT SQLID='ICFSSP00'`
> `
> CREATE TRIGGER ICFSSP00.REQT02_BI02
> NO CASCADE BEFORE INSERT ON REQT02
> REFERENCING NEW AS NEWT02
> FOR EACH ROW MODE DB2SQL
> CALL ICFSSP00.CHK_OWNER(NEWT02.OWNER) `
> --#SET TERMINATOR ;
> COMMIT;
> Because the owner must be an 8-character dataset qualifier, I return sqlstate 71002 if the owner
> is not an 8-char field.
> At run time, I consistently get sqlstate 71001 returned, with a message indicating that something
> has failed during a DELETE from a SYSIBM table.
> I assume this is the result table, which actually I don't expect to read.
>
>
> 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]
> Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
> ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

John Kliewe

RE: Stored Procedure to read flat files
(in response to James Campbell)

SYSIBM.TEXT_REC_INPUT and TEXT_REC_OUTPUT both have insert/update/delete to public.

Is there a way I can get more of the error message?  It tells me that something is going wrong during a delete from a SYSIBM table, but it doesn't tell me which one.

James Campbell

Stored Procedure to read flat files
(in response to John Kliewe)
Assuming you don't have any of the monitoring applications that give details of failing SQL
(Apptune, Detector, etc), run a performance class(3) trace, and have a look at the IFCID 58
record generated by the failing delete. This will have the actual SQLCA generated - sqlcode
and tokens. It will also have the package details (collid,name, contoken, statement number)
- you can use that to find the actual SQL text of the failing statement.

I have been having a mull over what you are trying to do. Having a check which involves
opening, reading and then closing a dataset for every insert does impose an overhead. Why
not have a secuity table which ICFSSP00.CHK_OWNER checks to see if the user is
authorised.

If you are determined to check for access to a dataset, then I would take
SDSNSAMP(DSNTWRS) and modify it to perform the security checks to see if the user has
the authority. Without actually accessing the dataset - discarding the stuff where it issues OS
commands . Yes, this would require a level of Assembler skills that not everyone has.

James Campbell

On 23 Jan 2018 at 6:05, John Kliewe wrote:

>
> SYSIBM.TEXT_REC_INPUT and TEXT_REC_OUTPUT both have insert/update/delete to public.
> Is there a way I can get more of the error message?  It tells me that something is going wrong
> during a delete from a SYSIBM table, but it doesn't tell me which one.
>

John Kliewe

RE: Stored Procedure to read flat files
(in response to James Campbell)

But I really do want this to work.  Regardless of whether this particular case is a good idea or not, I do think I should be able to use ADMIN_DS_BROWSE to read a dataset on a z/OS platform.

It's very likely that I'm doing it wrong -- is there any place I can go to find a working example of a trigger that calls ADMIN_DS_BROWSE to read a dataset on a z/OS machine?

Marcus Davage

Stored Procedure to read flat files
(in response to John Kliewe)
Classification: Public

I’m really curious now, and would like this to work, but I get a SQLCODE = -4743 even after a
SET CURRENT APPLICATION COMPATIBILITY = 'V11R1'
when I try to create your DDL. Both in V11CM and V11NFM.

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator
LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG http://www.idug.org/p/us/sn/uid=24500 | Twitter http://twitter.com/spufidoo | About.me http://about.me/spufidoo
From: John Kliewe [mailto:[login to unmask email]
Sent: 24 January 2018 16:06
To: [login to unmask email]
Subject: [DB2-L] - RE: Stored Procedure to read flat files

-- This email has reached the Bank via an external source --


But I really do want this to work. Regardless of whether this particular case is a good idea or not, I do think I should be able to use ADMIN_DS_BROWSE to read a dataset on a z/OS platform.

It's very likely that I'm doing it wrong -- is there any place I can go to find a working example of a trigger that calls ADMIN_DS_BROWSE to read a dataset on a z/OS machine?

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


Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555.

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.

Marcus Davage

Stored Procedure to read flat files
(in response to Marcus Davage)
Classification: Public

OK. (Thanks Colin.) APPLCOMPAT V11R1 on the CREATE PROCEDURE worked.
Now I get the same as you, John.

DSNT408I SQLCODE = -438, SQLSTATE = 70002, UNKNOWN SQLSTATE TYPE FROM SQL
ROUTINE TOKENS DSNA618I DSNADMDB SQL ERROR DURING SQL STATEMENT
DELETE, TABLE=SYSIBM

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator
LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG http://www.idug.org/p/us/sn/uid=24500 | Twitter http://twitter.com/spufidoo | About.me http://about.me/spufidoo
From: Davage, Marcus (ITS Database Services - DB2) [mailto:[login to unmask email]
Sent: 25 January 2018 10:36
To: [login to unmask email]
Subject: [DB2-L] - RE: Stored Procedure to read flat files

-- This email has reached the Bank via an external source --

Classification: Public
I’m really curious now, and would like this to work, but I get a SQLCODE = -4743 even after a
SET CURRENT APPLICATION COMPATIBILITY = 'V11R1'
when I try to create your DDL. Both in V11CM and V11NFM.

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator
LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG http://www.idug.org/p/us/sn/uid=24500 | Twitter http://twitter.com/spufidoo | About.me http://about.me/spufidoo
From: John Kliewe [mailto:[login to unmask email]
Sent: 24 January 2018 16:06
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Stored Procedure to read flat files

-- This email has reached the Bank via an external source --


But I really do want this to work. Regardless of whether this particular case is a good idea or not, I do think I should be able to use ADMIN_DS_BROWSE to read a dataset on a z/OS platform.

It's very likely that I'm doing it wrong -- is there any place I can go to find a working example of a trigger that calls ADMIN_DS_BROWSE to read a dataset on a z/OS machine?

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


Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555.

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.

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


Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555.

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.

John Kliewe

RE: Stored Procedure to read flat files
(in response to Marcus Davage)

I worked around this issue by creating a REXX stored proc instead.

But I don't want to give up.  The manual says that ADMIN_DS_BROWSE can be used to read a flat file.  How do you make it work from a trigger?  I'm also now interested in ADMIN_DS_WRITE

J&#248;rn Thyssen

RE: Stored Procedure to read flat files
(in response to John Kliewe)

Hi John,

Can you post the DDL for the trigger?

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

J&#248;rn Thyssen

RE: Stored Procedure to read flat files
(in response to John Kliewe)

Hi John,

This works for me:

--#SET TERMINATOR `                                                       
SET CURRENT SQLID='MYUSER'`
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","MYUSER" `
CREATE PROCEDURE MYUSER.DS_WRAP
(IN DSNAME CHAR( 44) FOR SBCS DATA CCSID EBCDIC)
VERSION V1
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
DYNAMIC RESULT SETS 0
ALLOW DEBUG MODE
PARAMETER CCSID EBCDIC
QUALIFIER MYUSER
PACKAGE OWNER MYUSER
ASUTIME NO LIMIT
COMMIT ON RETURN NO
INHERIT SPECIAL REGISTERS
WLM ENVIRONMENT FOR DEBUG MODE WLMENV1
NODEFER PREPARE
CURRENT DATA NO
DEGREE 1
DYNAMICRULES RUN
APPLICATION ENCODING SCHEME EBCDIC
WITHOUT EXPLAIN
WITHOUT IMMEDIATE WRITE
ISOLATION LEVEL CS
WITHOUT KEEP DYNAMIC
OPTHINT ''
RELEASE AT COMMIT
REOPT NONE
VALIDATE RUN
ROUNDING DEC_ROUND_HALF_EVEN
DECIMAL(15)
BUSINESS_TIME SENSITIVE YES
SYSTEM_TIME SENSITIVE YES
ARCHIVE SENSITIVE YES
P1:
BEGIN
DECLARE RETURN_CODE INTEGER ;
DECLARE MSG VARCHAR( 1331) ;
CALL "SYSPROC".ADMIN_DS_BROWSE(1, DSNAME, '', '', RETURN_CODE,
MSG) ;
IF RETURN_CODE <> 0 THEN
SIGNAL SQLSTATE '79999'
SET MESSAGE_TEXT = 'ADMIN_DS_BROWSE RETURN CODE ' !!
CHAR(RETURN_CODE) !! ' ' !! MSG ;
END IF ;
END P1 `
--#SET TERMINATOR ;
COMMIT;

------------------------------------------------------------------------
-- --
-- Database 2 Administration Tool (DB2 Admin) , program 5655-W34 --
-- --
-- ADB2GEN - Extract object definitions from the DB2 Catalog tables --
-- --
-- Input prepared on : PDS1 (1215 ) Extract time : 2018-06-23 08:29 --
-- This extract job actually run on a DB2 version 1210 system --
-- --
-- Catalog values overridden : none
-- --
-- Generate : SG=N DB=Y TS=Y TB=Y VW=N IX=N SY=N AL=N LB=N CM=N FK=N --
-- TG=Y UT=N UF=N SP=N SQ=N RO=N TC=N MK=N PM=N AC=N GV=N --
-- Grants : SG=N DB=N TS=N TB=N VW=N SC=N UT=N UF=N SP=N SQ=N GV=N --
-- --
------------------------------------------------------------------------
------------------------------------------------------------------------
-- --
-- ADB2GEN: Generate DDL for Table=MYUSER.TTRIGDS
-- --
------------------------------------------------------------------------
--
------------------------------------------------------------------------
-- Table=MYUSER.TTRIGDS
-- Database will be implicitly defined
-- Table space will be implicitly defined
------------------------------------------------------------------------
--
SET CURRENT SQLID='MYUSER';
--
CREATE TABLE MYUSER.TTRIGDS
("KEY" INTEGER NOT NULL,
"VALUE" CHAR(10) FOR SBCS DATA WITH DEFAULT NULL)
PARTITION BY SIZE EVERY 4 G
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE
APPEND NO ;
--
--#SET TERMINATOR `
--
------------------------------------------------------------------------
-- Triggers for Table=MYUSER.TTRIGDS
------------------------------------------------------------------------
--
--
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","MYUSER" `
--
CREATE TRIGGER MYUSER.JTRIGDS
AFTER INSERT ON MYUSER.TTRIGDS
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
CALL MYUSER.DS_WRAP('MYUSER.ADB.IGNORE2') ;
END `
--
--#SET TERMINATOR ;
--
COMMIT;
--
------------------------------------------------------------------------
-- ADB2GEN - End of generated DDL --
------------------------------------------------------------------------
--

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

J&#248;rn Thyssen

RE: Stored Procedure to read flat files
(in response to Jørn Thyssen)

and with Db2 12 advanced triggers this will work:

------------------------------------------------------------------------
-- --
-- Database 2 Administration Tool (DB2 Admin) , program 5655-W34 --
-- --
-- ADB2GEN - Extract object definitions from the DB2 Catalog tables --
-- --
-- Input prepared on : PDS1 (1215 ) Extract time : 2018-06-23 11:20 --
-- This extract job actually run on a DB2 version 1210 system --
-- --
-- Catalog values overridden : none
-- --
-- Generate : SG=N DB=Y TS=Y TB=Y VW=Y IX=Y SY=Y AL=Y LB=Y CM=Y FK=Y --
-- TG=Y UT=N UF=N SP=N SQ=N RO=N TC=N MK=Y PM=Y AC=Y GV=N --
-- Grants : SG=N DB=N TS=N TB=N VW=N SC=N UT=N UF=N SP=N SQ=N GV=N --
-- --
------------------------------------------------------------------------
------------------------------------------------------------------------
-- --
-- ADB2GEN: Generate DDL for Table=MYUSER.TTRIGDS
-- --
------------------------------------------------------------------------
--
------------------------------------------------------------------------
-- Table=MYUSER.TTRIGDS
-- Database will be implicitly defined
-- Table space will be implicitly defined
------------------------------------------------------------------------
--
CREATE TABLE MYUSER.TTRIGDS
("KEY" INTEGER NOT NULL,
"VALUE" CHAR(10) FOR SBCS DATA WITH DEFAULT NULL)
PARTITION BY SIZE EVERY 4 G
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE
APPEND NO ;
--
COMMIT;
--
--#SET TERMINATOR `
--
------------------------------------------------------------------------
-- Triggers for Table=MYUSER.TTRIGDS
------------------------------------------------------------------------
--
SET CURRENT SCHEMA='MYUSER'`
--
--
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","MYUSER" `
--
CREATE TRIGGER MYUSER.JTRIGDS_V12
AFTER INSERT ON MYUSER.TTRIGDS
FOR EACH ROW
BEGIN ATOMIC
DECLARE RETURN_CODE INTEGER ;
DECLARE MSG VARCHAR( 1331) ;
CALL "SYSPROC".ADMIN_DS_BROWSE(1, 'MYUSER.ADB.IGNORE2', '', '',
RETURN_CODE, MSG) ;
IF RETURN_CODE <> 0 THEN
SIGNAL SQLSTATE '79999' SET MESSAGE_TEXT =
'ADMIN_DS_BROWSE RETURN CODE ' !! CHAR(RETURN_CODE) !! ' '
!! MSG ;
END IF ;
END `
--
COMMIT`
--
--#SET TERMINATOR ;
--
------------------------------------------------------------------------
-- ADB2GEN - End of generated DDL --
------------------------------------------------------------------------
--

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

John Kliewe

RE: Stored Procedure to read flat files
(in response to Jørn Thyssen)

Jorn,

I must have something wrong in my environment.

I get an SQLCODE -817 when the admin proc tries to delete from sysibm.text_rec_output :

DSNT408I SQLCODE = -817, ERROR: THE SQL STATEMENT CANNOT BE EXECUTED
BECAUSE THE STATEMENT WILL RESULT IN A PROHIBITED DATA CHANGE
OPERATION
DSNT418I SQLSTATE = 25000 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXERT SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -250 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF06' X'00000000' X'00000000'
X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

SQLCAID SQLCA
SQLCABC 136
SQLCODE -817
SQLERRML 00

I do have SELECT,INSERT,DELETE auth on sysibm.text_rec_output. Any idea what this could be ?

J&#248;rn Thyssen

RE: Stored Procedure to read flat files
(in response to John Kliewe)

Hi John,

Do any of the situations documented in the manual for -817 apply to you?

if not it is PMR time...

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

John Kliewe

RE: Stored Procedure to read flat files
(in response to Jørn Thyssen)

Jorn - thanks for your help!  I realize now that I had misunderstood your example, and my failure was due to the fact that I was trying to implement it with a BEFORE trigger.  Since ADMIN_DS_BROWSE does in fact update a temporary table, it's not valid in a BEFORE trigger.

Anyway - your example worked perfectly and I have integrated into my environment here.  Thanks very much!

John Kliewe

RE: Stored Procedure to read flat files
(in response to John Kliewe)

On to the next issue :

Before I got ADMIN_DS_BROWSE working correctly, I had created a REXX external proc to do the same thing.  One nice thing about the extermal REXX proc is the SECURITY parameter.   SECURITY can be DB2, USER, or DEFINER. This was good for me because I could create a file and NOT grant read access on it to my end user.  The only way my end user can read the file is by going through the Db2 stored proc.  This is the whole point.

 

But if I call ADMIN_DS_BROWSE from a Native SQL proc, the SECURITY parameter isn't available.  I want the end user to be able to browse the dataset through the stored proc, but NOT from their own userid.  This is possible with an EXTERNAL REXX proc, but I don't see how to do it for a Native SQL proc.

 

Any advice?