Setting Variables Inside DB2 Script

[jtrapat1]

I'm using DB2 version 7 and I'm writing several scripts to be executed from the command line.
These are simple inserts.
Here's an example script:
-------------------------------------------
db2 "INSERT INTO nysa.bp37t_temp_approp_distinct SELECT DISTINCT budget_yr, agency_code, substr(prog_code,2,3), category, fund_type, substr(dob_object,1,1),0, sum(summary_value),0,0 FROM nysa.bp30t_dob_bps WHERE module = 'APPR' AND group_type = 'UNIVERSAL_OBJECT' AND dob_yr = '2001' AND event_name = 'Exec Budgt Rec' GROUP BY (budget_yr,agency_code,substr(prog_code,2,3),category,fund_type,substr(dob_object,1,1))"
--------------------------------------------------
My question is this:
Since the only thing that changes in this script when it is executed from year to year is the dob_yr , we would like to assign variables for the different years.
Then, these could be assigned to the current year at the top of the script.

Is it possible to do this against db2?

Thanks in Advance.
John

 

[blom0344]

Hello John,

To expand a bit on your first thread, if you want to work with interactive SQL that prompts you for a variable you will have to call the SQL from within a stored procedure. I do not think you can use it with a static SQL, cause you need something to store input in. Bare SQL will not provide it. The syntax for a variable in such a SQL is like:

......................
AND dob_yr = :var1 ..........
.............

At the IBM website you can find excellent PDF manuals (Redbooks) on how to build diverse kind of stored procedures using SQL or Java. Control center lets you build Stored Procedure with a special Stored Procedure Wizard.
For SQL stored procedure you need a C-compiler.

You could also alter the SQL with a range on dob_yr:

....................
AND dob_yr IN ('2001','2002')..............
.................... T. Blom
Information analyst
tbl@shimano-eu.com

 

[jtrapat1]

T. Blom,
Thanks for the response.
The interactive input method isn't such a big deal to me.
I don't need to be prompted for the different input variables.
Ideally, I would like to change these at the top of the script before I run it; say, something like:

current_year = 2001;
previous_year = 2000;

Can this be done in a sql script?

Thanks
John

 

[jtrapat1]

T. Blom,
This is another minor question but related to my script above.

If I have about four or five sql statements in a script and I run this, can I log the messages to a text file?

I tried db2 messages msgs.txt on a line by itself but this didn't work.

Thanks
John