Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL statements vs reading files in a DB2 Stored Procedure

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
akriti

New User


Joined: 03 Sep 2009
Posts: 8
Location: India

PostPosted: Thu Sep 03, 2009 3:45 pm    Post subject: SQL statements vs reading files in a DB2 Stored Procedure
Reply with quote

Hi,

I am working on a new requirement of replacing a table with a flat file.I want to know the pros and cons of replacing the SQL statements in a Stored Procedure with equivalent read commands on a dataset.

I want to know what will be the effect of replacing the SQL statements by equivalent read statements.

Another point to note is that the SP might contain some other SQL queries on other tables which will not be replaced.

How will system performance and cost be affected if some SQL statements are replaced.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 03, 2009 4:55 pm    Post subject:
Reply with quote

can you define and access 'files' from a DB2 Stored Procedure?
Back to top
View user's profile Send private message
akriti

New User


Joined: 03 Sep 2009
Posts: 8
Location: India

PostPosted: Thu Sep 03, 2009 5:12 pm    Post subject:
Reply with quote

access files means reading a file
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 03, 2009 5:14 pm    Post subject:
Reply with quote

akriti wrote:
access files means reading a file


why don't you answer my question?

can you define, allocate and access a file with a db2 stored procedure?
Back to top
View user's profile Send private message
akriti

New User


Joined: 03 Sep 2009
Posts: 8
Location: India

PostPosted: Thu Sep 03, 2009 5:18 pm    Post subject:
Reply with quote

I dont want to allocate a file with the SP.
i just want to read this already existing flat file.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 03, 2009 5:25 pm    Post subject:
Reply with quote

maybe someone else can help you.
Back to top
View user's profile Send private message
akriti

New User


Joined: 03 Sep 2009
Posts: 8
Location: India

PostPosted: Thu Sep 03, 2009 6:16 pm    Post subject:
Reply with quote

For reading the flat file ,I can call a cobol subroutine.
Back to top
View user's profile Send private message
akriti

New User


Joined: 03 Sep 2009
Posts: 8
Location: India

PostPosted: Thu Sep 03, 2009 6:22 pm    Post subject:
Reply with quote

I wanted to know if a CALL to a Cobol subroutine can effectively replace a SQL query in a Stored Procedure
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Thu Sep 03, 2009 6:23 pm    Post subject: Reply to: SQL statements vs reading files in a DB2 Stored Pr
Reply with quote

hi,

Would you read the record from a file and put into a array....then search it as per your condition......kindly reply
Back to top
View user's profile Send private message
akriti

New User


Joined: 03 Sep 2009
Posts: 8
Location: India

PostPosted: Thu Sep 03, 2009 6:28 pm    Post subject:
Reply with quote

This is not the primary question, first I want to know if a subroutine CALL can replace a SQL statement without effecting the performance of the system..?

After reading, I will not put it into the array
I dont want the whole record of the file I just want to retrieve data present at specific positions as per the requirement.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Sep 03, 2009 6:43 pm    Post subject:
Reply with quote

akriti wrote:
This is not the primary question, first I want to know if a subroutine CALL can replace a SQL statement without effecting the performance of the system..?

After reading, I will not put it into the array
I dont want the whole record of the file I just want to retrieve data present at specific positions as per the requirement.


When you do a read you get the whole record. You still have to associate (allocate) the assign name in the program to a DSN, how are you going to do that.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Thu Sep 03, 2009 6:57 pm    Post subject:
Reply with quote

Can a subroutine reading a sequential file replace the SQL in the Stored Procedure?

Sure it can.

Will it be effective? That depends.

In order for it to be ‘equivalent’, the cursor processing in the SP (I assume you are processing a cursor) must be retrieving the same number of rows in the same order that you will be processing in your sequential (flat) file.

The factors that would determine the effectiveness are:
How many rows are on the DB2 table? What percentage are in the cursor?
Is an index being accessed for the cursor?
These all determine how well your current process works.

How many records are in the sequential file?
Is this the driver of the process?
Are you processing every record?

You a asking a very general question with regards to a very specific situation.

I am not sure how you can expect a specific answer.

So in summary, you can do it.
Run parallel tests with LARGE volumes of data (otherwise the results are meaningless).
Then and only then will you know the cost savings if any.
Back to top
View user's profile Send private message
akriti

New User


Joined: 03 Sep 2009
Posts: 8
Location: India

PostPosted: Thu Sep 03, 2009 7:30 pm    Post subject:
Reply with quote

Thanks Dave.

Have u considered the cost of calling the subroutine from the SP while mentioning the scenarios.

I have also read that its not possible to call a subroutine directly from the SP, I need to use an external procedure to do so.

Considering all this , is it really feasible to replace the SQL statements with the subroutine call.

Appreciate your help.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Thu Sep 03, 2009 7:48 pm    Post subject:
Reply with quote

Quote:
Have u considered the cost of calling the subroutine from the SP while mentioning the scenarios.


Have ‘I’ considered the cost or have ‘you’ considered the cost of calling the subroutine?
It is another factor (of many). The only way to really know is to try it.

Quote:
I have also read that its not possible to call a subroutine directly from the SP, I need to use an external procedure to do so.


You can call a subroutine directly from a SP. The Loadlib where it resides must be in the concatenation for the Workload Manager.

Craq does bring up a good point regarding file allocation. In my experience, the subroutines that are called from the SP (which we do often), do not perform any sequential I/O. They may have their own SQL however. At any rate, you would need some sort of dynamic file allocation to have a DD/DSN relationship established so that your Select statement in the subroutine can make the connection.

Quote:
Considering all this , is it really feasible to replace the SQL statements with the subroutine call.

I seems to me that you want someone to say ‘no it is not feasable’ to do so.
I am not going to say that.
Do you want to do this, again it depends on the specifics of your situation, which only you know.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Sep 03, 2009 9:06 pm    Post subject:
Reply with quote

Hello,

Quote:
Considering all this , is it really feasible to replace the SQL statements with the subroutine call.
It is really difficult for anyone to help as you have not posted the requirement. You might believe you've explained the requirement, but you have not. . .

Quote:
I dont want the whole record of the file I just want to retrieve data present at specific positions as per the requirement.
What kind of sequential read are you thinking of?

Quote:
first I want to know if a subroutine CALL can replace a SQL statement without effecting the performance of the system..?
Depends on what is "called". . .

If you post the real requirement rather than continue this hodge-podge, more useful replies might be posted.
Back to top
View user's profile Send private message
akriti

New User


Joined: 03 Sep 2009
Posts: 8
Location: India

PostPosted: Fri Sep 04, 2009 10:43 am    Post subject:
Reply with quote

Thanks Dick.

Let me explain in a series of steps:
1) We have existing SP's which have Select commands on 3 tables.

2) Now we will be recieving the feed from the 3 tables combined in the form of a flat file (this file will contain all data from the 3 tables).

3) I want to replace the Select commands in the SP with the corresponding read of the paticular column in the file.
Ex: If the Select query selects Col A based on some condition, I sould be able to read col A from the file, validating the same condition.

4) After reading the file ,the SP should return the same O/p as it was returning after performing Select on the table.

Now I want to know how can I implement this scenario in cost effective manner.

Please reply asap if more details are needed.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 04, 2009 12:53 pm    Post subject:
Reply with quote

you should load the 'file' into a global temp table (or an existing standard table), and then invoke the SP.

SP's execute in a different space as COBOL programs, once you invoke the SP, you want to stay there and perform the work.
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Sun Sep 06, 2009 3:24 am    Post subject: Re: SQL statements vs reading files in a DB2 Stored Procedur
Reply with quote

akriti wrote:
Hi,

I am working on a new requirement of replacing a table with a flat file.I want to know the pros and cons of replacing the SQL statements in a Stored Procedure with equivalent read commands on a dataset.

I want to know what will be the effect of replacing the SQL statements by equivalent read statements.

Another point to note is that the SP might contain some other SQL queries on other tables which will not be replaced.

How will system performance and cost be affected if some SQL statements are replaced.


FILE SECTION is not restricted for SP. So you can. How ?

1. You can use dynamic file allocation
2. You can save the whole file as BLOB and then download in batch program
3. You can CALL SP from the batch program and then load the flat file from RS of RETURN CURSOR(s)
4. Instead of SP you can use UDF TABLE FUNCTION and create file on result of function
5. You can connect to CICS thru SP

Lenny icon_wink.gif icon_wink.gif icon_wink.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us