SQL statements vs reading files in a DB2 Stored Procedure

akriti · New User Joined: 03 Sep 2009 Posts: 8 Location: India

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.

dbzTHEdinosauer · Posted: Thu Sep 03, 2009 4:55 pm

can you define and access 'files' from a DB2 Stored Procedure?

akriti · New User Joined: 03 Sep 2009 Posts: 8 Location: India

access files means reading a file

dbzTHEdinosauer · Posted: Thu Sep 03, 2009 5:14 pm

akriti · New User Joined: 03 Sep 2009 Posts: 8 Location: India

I dont want to allocate a file with the SP.
i just want to read this already existing flat file.

dbzTHEdinosauer · Posted: Thu Sep 03, 2009 5:25 pm

maybe someone else can help you.

akriti · New User Joined: 03 Sep 2009 Posts: 8 Location: India

For reading the flat file ,I can call a cobol subroutine.

akriti · New User Joined: 03 Sep 2009 Posts: 8 Location: India

I wanted to know if a CALL to a Cobol subroutine can effectively replace a SQL query in a Stored Procedure

bhairon singh rathore · New User Joined: 19 Jun 2008 Posts: 91 Location: banglore

hi,

Would you read the record from a file and put into a array....then search it as per your condition......kindly reply

akriti · New User Joined: 03 Sep 2009 Posts: 8 Location: India

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.

Craq Giegerich · Posted: Thu Sep 03, 2009 6:43 pm

daveporcelan · Posted: Thu Sep 03, 2009 6:57 pm

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.

akriti · New User Joined: 03 Sep 2009 Posts: 8 Location: India

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.

daveporcelan · Posted: Thu Sep 03, 2009 7:48 pm

dick scherrer · Posted: Thu Sep 03, 2009 9:06 pm

Hello,

akriti · New User Joined: 03 Sep 2009 Posts: 8 Location: India

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.

dbzTHEdinosauer · Posted: Fri Sep 04, 2009 12:53 pm

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.

lkhiger · New User Joined: 28 Oct 2005 Posts: 89