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
 

 

Set Temporary Variable in DB2 Spufi Member

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

New User


Joined: 27 Mar 2008
Posts: 31
Location: Karlsruhe / Germany

PostPosted: Mon Jun 16, 2008 3:29 pm    Post subject: Set Temporary Variable in DB2 Spufi Member
Reply with quote

Hi,

i hava a spufi member with several selects and deletes.

Code:
Delete from table.table1 where Keynumber = 1234 and Secondnumber = 54323;

insert into table.table1 (col1, col2, col3)
select col1, col2, col3
where Keynumber = 1234 and Secondnumber = 54323;

....
....


Is it possible to set a variable at first step so that i just have to change once the value of keynumber and secondnumber.

I tried something like that:

Code:
SET KEYNUM = 1234;
SET SECONNUM = 54323;


Delete from table.table1 where Keynumber = KEYNUM and Secondnumber = SECONNUM;

insert into table.table1 (col1, col2, col3)
select col1, col2, col3
where Keynumber = KEYNUM and Secondnumber = SECONNUM;

....
....


This example does not work, but is something like this possible in spufi? We have DB2 Version 8, and 9 will follow soon.

Regards
Andi
Back to top
View user's profile Send private message

birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Mon Jun 16, 2008 3:58 pm    Post subject:
Reply with quote

I guess In SPUFI member, You can use "CHG" or "CHG ALL" command to replace. I think this will work. Then why you need "SET" command
Back to top
View user's profile Send private message
Andi1982

New User


Joined: 27 Mar 2008
Posts: 31
Location: Karlsruhe / Germany

PostPosted: Mon Jun 16, 2008 4:06 pm    Post subject:
Reply with quote

Normally this was the way i did my changes. But sometimes
KEYNUM = 1234
and SECONNUM = 1234

but then
KEYNUM should be 5689
and SECONNUM should be 21454.

So the "change all" command is not possible anymore. I thought it would be easier to have one part in member where i can define the used variables.

My example is just a little one to show you my problem. I have much more Variables in my spufi. But i think i have to do this changes manual anymore.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jun 17, 2008 12:24 am    Post subject:
Reply with quote

If I am remembering correctly, you can set variables if you wrap your SQL in a BEGIN and END SQL statement.
Back to top
View user's profile Send private message
Andi1982

New User


Joined: 27 Mar 2008
Posts: 31
Location: Karlsruhe / Germany

PostPosted: Tue Jun 17, 2008 11:11 am    Post subject:
Reply with quote

hm,

i can't find such syntax anywhere. I got an error when i tried this.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Jun 17, 2008 2:00 pm    Post subject:
Reply with quote

if you would write your sql with conditions on separate lines, you could use a combination of x all, chg all nx.
Back to top
View user's profile Send private message
UmeySan

Active Member


Joined: 22 Aug 2006
Posts: 743
Location: Germany

PostPosted: Tue Jun 17, 2008 5:23 pm    Post subject:
Reply with quote

Hi !

As i remember, BEGINN & END encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed. BEGIN and END are control-of-flow language keywords for Transact-SQL.
Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL language.
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 find particular member name in PDS us... ravi243 CLIST & REXX 10 Mon Dec 19, 2016 6:44 pm
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 7 Fri Nov 18, 2016 1:48 pm
No new posts Format to pipe delimited with variabl... pshongal SYNCSORT 6 Wed Sep 14, 2016 2:48 pm
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts OPC JCL Variable passion_sujesh IBM Tools 5 Fri Jul 01, 2016 4:21 am


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