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.