| View previous topic :: View next topic |
| Author |
Message |
ranga_subham
Intermediate
Joined: 31 Jan 2006
Posts: 255
Topics: 72
|
 Posted: Thu Jan 24, 2008 10:10 am Post subject: How to Automate this? |
 |
|
Hi,
We have a DB2-COBOL program that uses a SQL query (Cursor) to pull information from a DB2 table then proceeds further.
This SQL query pulls data based on a where condition (AAC = '1','2','3')
The thing about this program is, it often undergoes changes whenever the client asks a new AAC is to be added to the program to be pulled out into the report.
We want to automize this process. I mean, instead of each time retreiving it to test, code it, productionize it, we want a way through which the program retreival from Endevor is avoided and client can enter the AAC by themselves.
Please suggest.
TIA.
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard |
|
| Back to top |
|
 |
videlord
Beginner
Joined: 09 Dec 2004
Posts: 147
Topics: 19
|
| Posted: Thu Jan 24, 2008 10:14 am Post subject: |
|
|
i'm not sure understanding your request
but i think it's easy to be done using dynamical SQL, prepare the SQL statement after reading input |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Thu Jan 24, 2008 10:18 am Post subject: Re: How to Automate this? |
|
|
| ranga_subham wrote: | Hi,
We have a DB2-COBOL program that uses a SQL query (Cursor) to pull information from a DB2 table then proceeds further.
This SQL query pulls data based on a where condition (AAC = '1','2','3')
The thing about this program is, it often undergoes changes whenever the client asks a new AAC is to be added to the program to be pulled out into the report.
We want to automize this process. I mean, instead of each time retreiving it to test, code it, productionize it, we want a way through which the program retreival from Endevor is avoided and client can enter the AAC by themselves.
Please suggest.
TIA. |
Create a table with the AAC that need to be processed and use that to drive the report program. You would have to add a new process to allow the client to maintain the AAC table.
Or create a procedure for the client to request the report and pass the AAC as a parameter. It wouldn't require dynamic SQL just use a host variable to specify the AAC. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 24, 2008 6:25 pm Post subject: |
|
|
ranga_subham,
| Quote: | | This SQL query pulls data based on a where condition (AAC = '1','2','3') |
Move the account values to a File which will be appended with every new account you add to it. Now read this file as input and open the cursor with cond AAC = ' account # from file' and you repeat the process until the end of the file. In that way you never have to change the program at all
Hope this helps...
Cheers
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
ranga_subham
Intermediate
Joined: 31 Jan 2006
Posts: 255
Topics: 72
|
| Posted: Tue Jan 29, 2008 5:48 am Post subject: |
|
|
Kolusu,
Will we have several OPEN, FETCH & CLOSE because we will process each AAC one time?  
How about if a given AAC is not found in the database (I assume that we will have to handle the Not Found condition and proceed to next read !!)   
May I ask you to elaborate on your suggestion please. 
TIA.
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Jan 29, 2008 11:05 am Post subject: |
|
|
| Quote: | | Will we have several OPEN, FETCH & CLOSE because we will process each AAC one time? :shocked |
ranga_subham,
of course we will have multiple open/fetch/close. Given that you don't want to do any of the following what other options have you got?
1. Dynamic Sql
2. Add a new column on the table
3. Change the pgm every time a new account is added
Trust me the overhead of open and close isn't gonna hurt your performance unless you process like a million accounts.
And for the second Q, it is the duty of the programmer to handle exceptions.
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Tue Jan 29, 2008 2:30 pm Post subject: |
|
|
| A slight modification on Kolusu's idea. Create a DECLARED GLOBAL TEMPORARY TABLE in your program. Read the records from the file and insert rows to the temp tbl. Modify the cursor SQL to join to the temp table on the account. Same idea with a minor twist might save a couple lines of code. |
|
| Back to top |
|
|
ranga_subham
Intermediate
Joined: 31 Jan 2006
Posts: 255
Topics: 72
|
| Posted: Thu Jan 31, 2008 5:42 am Post subject: |
|
|
Thanks to Kolusu & jsharon1248. 
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
