Hello
firts of all, apologies if this is not the right forum
I have a file with many records... I need update a db2 table with this file. Table db2 many records but I only want to update the table with the records of the file.
If I had a record, do not file an update but since the sentence is not fixed where it will change depending on the content of the file, I wonder if you can do.
I remember a time I did something. Through an updated file a report table records the WHERE statement and SET fields with the contents of the file. Am I wrong?
Thanks a lot of
update table with more records
update table with more records
by pulcinella » Tue Jan 22, 2013 8:56 pm
- pulcinella
- Posts: 114
- Joined: Mon Dec 10, 2007 10:18 pm
- Has thanked: 0 time
- Been thanked: 0 time
Re: update table with more records
by Akatsukami » Tue Jan 22, 2013 9:03 pm
This is really not comprehensible. I recommend that you recast your requirement in better English."You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
-
Akatsukami - Global moderator
- Posts: 1058
- Joined: Sat Oct 16, 2010 2:31 am
- Location: Bloomington, IL
- Has thanked: 6 times
- Been thanked: 51 times
Re: update table with more records
by dick scherrer » Tue Jan 22, 2013 9:39 pm
Hello,
And show some sample input data (table and file) and the output table content you want when your process is run.
And show some sample input data (table and file) and the output table content you want when your process is run.
Hope this helps,
d.sch.
d.sch.
-
dick scherrer - Global moderator
- Posts: 6268
- Joined: Sat Jun 09, 2007 8:58 am
- Has thanked: 3 times
- Been thanked: 93 times
Re: update table with more records
by pulcinella » Wed Jan 23, 2013 10:51 pm
Akatsukami, I think that it is not problem of expression
Well, if I have the following table, the key is column A
column A column B
-------- --------
A 1.000$
B 2.000$
C 500$
D 7.000$
E 7.500$
F 1.250$
G 3.600$
H 7.210$
......
Z 240$
and I want to update only 3 values (for example):
A 2.500$
C 1.200$
G 1.550$
I can use the UPDATE statement and the WHERE clause. That's not a problem
But if I have to update an undetermined number of records by JCL. Instead of upgrading three records, I had to update 10 records today, tomorrow 30 and last five. Can I use a file with the values of the where clause and the values of the SET?
Well, if I have the following table, the key is column A
column A column B
-------- --------
A 1.000$
B 2.000$
C 500$
D 7.000$
E 7.500$
F 1.250$
G 3.600$
H 7.210$
......
Z 240$
and I want to update only 3 values (for example):
A 2.500$
C 1.200$
G 1.550$
I can use the UPDATE statement and the WHERE clause. That's not a problem
But if I have to update an undetermined number of records by JCL. Instead of upgrading three records, I had to update 10 records today, tomorrow 30 and last five. Can I use a file with the values of the where clause and the values of the SET?
- pulcinella
- Posts: 114
- Joined: Mon Dec 10, 2007 10:18 pm
- Has thanked: 0 time
- Been thanked: 0 time
Re: update table with more records
by dick scherrer » Thu Jan 24, 2013 12:55 am
Hello,
It is completely unclear as to what you are trying to accomplish. What does the data posted have to do with the original "question"?
When the process ends, what should the table contain?
You have shown what i believe to be keys, but nothing about the update(s) to be done.
It is completely unclear as to what you are trying to accomplish. What does the data posted have to do with the original "question"?
How do you know whi9ch rows? How would we know?and I want to update only 3 values (for example):
When the process ends, what should the table contain?
You have shown what i believe to be keys, but nothing about the update(s) to be done.
Hope this helps,
d.sch.
d.sch.
-
dick scherrer - Global moderator
- Posts: 6268
- Joined: Sat Jun 09, 2007 8:58 am
- Has thanked: 3 times
- Been thanked: 93 times
Re: update table with more records
by Akatsukami » Thu Jan 24, 2013 1:18 am
pulcinella wrote:Well, if I have the following table, the key is column A
column A column B
-------- --------
A 1.000$
B 2.000$
C 500$
D 7.000$
E 7.500$
F 1.250$
G 3.600$
H 7.210$
......
Z 240$
and I want to update only 3 values (for example):
A 2.500$
C 1.200$
G 1.550$
I can use the UPDATE statement and the WHERE clause. That's not a problem
But if I have to update an undetermined number of records by JCL. Instead of upgrading three records, I had to update 10 records today, tomorrow 30 and last five. Can I use a file with the values of the where clause and the values of the SET?
Well, I am not a COBOL programmer (this century, anyway) but in PL/I or Rexx this would be a rather trivial program, and I have a hard time believing that it is less so in COBOL. Do you have a reason for not writing such a program?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
-
Akatsukami - Global moderator
- Posts: 1058
- Joined: Sat Oct 16, 2010 2:31 am
- Location: Bloomington, IL
- Has thanked: 6 times
- Been thanked: 51 times
Re: update table with more records
by Akatsukami » Thu Jan 24, 2013 2:37 am
FTR, I created the table, and wrote and ran a Rexx script to do this in 45 minutes (although that script will be worthless to most people because it uses Open Software's RXTASQL interface to address DB2 rather than IBM's DSNREXX). When I was at university (shortly after end of the last ice age 
), one of my professors told me, "If your program runs correctly the first time, it was too trivial to be worth writing". As I suspected, this program was too trivial to be worth writing.
), one of my professors told me, "If your program runs correctly the first time, it was too trivial to be worth writing". As I suspected, this program was too trivial to be worth writing."You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
-
Akatsukami - Global moderator
- Posts: 1058
- Joined: Sat Oct 16, 2010 2:31 am
- Location: Bloomington, IL
- Has thanked: 6 times
- Been thanked: 51 times
Re: update table with more records
by dick scherrer » Thu Jan 24, 2013 10:42 pm
Hello,
And i still do not understand the "requirement" . . . .
d
And i still do not understand the "requirement" . . . .
d
Hope this helps,
d.sch.
d.sch.
-
dick scherrer - Global moderator
- Posts: 6268
- Joined: Sat Jun 09, 2007 8:58 am
- Has thanked: 3 times
- Been thanked: 93 times
Re: update table with more records
by Akatsukami » Thu Jan 24, 2013 11:16 pm
dick scherrer wrote:Hello,
And i still do not understand the "requirement" . . . .
d
Well, if I misunderstood it, I didn't write the program correctly...
As I understand it: the TS has a table with at least two columns: dbkey and dbamount. A data set contains at least two fields, pskey and psamount. The SQL statement is:
UPDATE table
SET DBAMOUNT = PSAMOUNT
WHERE DBKEY = PSKEY
SET DBAMOUNT = PSAMOUNT
WHERE DBKEY = PSKEY
And that's it! The only thing that bewilders the TS is, since the number of records in the data set varies from day to day, there's no way to write a simple SPUFI to do this; an actual program (though, as I've said, a very trivial one) has to be written.
FTR, the program that I wrote was:
/* Rexx */
trace o
call RXSUBCOM "ADD", "SQL", "RXTASQL"
call DSNALI "OPEN", "HD0D", "RXTCS"
"EXECIO 1 DISKR TULIN"
do while (rc=0)
pull record
parse var record key amt
address sql "SELECT * FROM DDFHD0D.SHGB.ABBAS",
"WHERE COLUMN_A = '"key"'"
select
when (rc¬=0) then do
line = "RC =" rc " encountered when checking for key ["key"]"
push line
"EXECIO 1 DISKW TULPRINT"
end
when (sqlca.sqlcode<0) then do
line = "SQLCODE =" sqlca.sqlcode " encountered when checking",
"for key ["key"]"
push line
"EXECIO 1 DISKW TULPRINT"
end
when (sqlca.sqlrows=0) then do
line = "Key ["key"] not found on ABBAS"
push line
"EXECIO 1 DISKW TULPRINT"
end
when (sqlca.sqlrows>1) then do
line = "Multiple instances of key ["key"] found on ABBAS"
push line
"EXECIO 1 DISKW TULPRINT"
end
otherwise do
address sql "UPDATE DDFHD0D.SHGB.ABBAS",
" SET COLUMN_B = "amt,
" WHERE COLUMN_A = '"key"'"
select
when (rc¬=0) then do
line = "RC =" rc " encountered when checking for key ["key"]"
push line
"EXECIO 1 DISKW TULPRINT"
end
when (sqlca.sqlcode<0) then do
line = "SQLCODE =" sqlca.sqlcode " encountered when checking",
"for key ["key"]"
push line
"EXECIO 1 DISKW TULPRINT"
end
otherwise do
line = "Updated amount from" column_b.1 "to" amt "for",
"key ["key"]"
push line
"EXECIO 1 DISKW TULPRINT"
end
end
end
end
"EXECIO 1 DISKR TULIN"
end
trace o
call RXSUBCOM "ADD", "SQL", "RXTASQL"
call DSNALI "OPEN", "HD0D", "RXTCS"
"EXECIO 1 DISKR TULIN"
do while (rc=0)
pull record
parse var record key amt
address sql "SELECT * FROM DDFHD0D.SHGB.ABBAS",
"WHERE COLUMN_A = '"key"'"
select
when (rc¬=0) then do
line = "RC =" rc " encountered when checking for key ["key"]"
push line
"EXECIO 1 DISKW TULPRINT"
end
when (sqlca.sqlcode<0) then do
line = "SQLCODE =" sqlca.sqlcode " encountered when checking",
"for key ["key"]"
push line
"EXECIO 1 DISKW TULPRINT"
end
when (sqlca.sqlrows=0) then do
line = "Key ["key"] not found on ABBAS"
push line
"EXECIO 1 DISKW TULPRINT"
end
when (sqlca.sqlrows>1) then do
line = "Multiple instances of key ["key"] found on ABBAS"
push line
"EXECIO 1 DISKW TULPRINT"
end
otherwise do
address sql "UPDATE DDFHD0D.SHGB.ABBAS",
" SET COLUMN_B = "amt,
" WHERE COLUMN_A = '"key"'"
select
when (rc¬=0) then do
line = "RC =" rc " encountered when checking for key ["key"]"
push line
"EXECIO 1 DISKW TULPRINT"
end
when (sqlca.sqlcode<0) then do
line = "SQLCODE =" sqlca.sqlcode " encountered when checking",
"for key ["key"]"
push line
"EXECIO 1 DISKW TULPRINT"
end
otherwise do
line = "Updated amount from" column_b.1 "to" amt "for",
"key ["key"]"
push line
"EXECIO 1 DISKW TULPRINT"
end
end
end
end
"EXECIO 1 DISKR TULIN"
end
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
-
Akatsukami - Global moderator
- Posts: 1058
- Joined: Sat Oct 16, 2010 2:31 am
- Location: Bloomington, IL
- Has thanked: 6 times
- Been thanked: 51 times
Re: update table with more records
by dick scherrer » Fri Jan 25, 2013 1:09 am
Howdy,
Thanks for the clarification - my dain brammage never got there . . .
So the "file" could have been read and the SQL generated into a temporary file, then run in a second step that executed these update queries (i.e. "batch" SPUFI). . .?
Thanks for the clarification - my dain brammage never got there . . .
So the "file" could have been read and the SQL generated into a temporary file, then run in a second step that executed these update queries (i.e. "batch" SPUFI). . .?
Hope this helps,
d.sch.
d.sch.
-
dick scherrer - Global moderator
- Posts: 6268
- Joined: Sat Jun 09, 2007 8:58 am
- Has thanked: 3 times
- Been thanked: 93 times
-
- Related topics
- Replies
- Views
- Last post
-
- Tool to insert/delete/update records in a table?
by sam_hyd_1122 » Wed Nov 28, 2012 7:38 am - 1 Replies
- 2212 Views
- Last post by Akatsukami
Wed Nov 28, 2012 3:42 pm
- Tool to insert/delete/update records in a table?
-
- How to update DB2 table using Utility
1, 2by ritikasingh » Thu Apr 14, 2011 7:00 pm - 18 Replies
- 13468 Views
- Last post by pmartyn
Wed Apr 24, 2013 4:23 am
- How to update DB2 table using Utility
-
- UPDATE A TABLE INCASE OF JOINS
by djprakash1ml » Wed Jun 29, 2011 3:09 pm - 2 Replies
- 4247 Views
- Last post by GuyC
Thu Jun 30, 2011 12:56 pm
- UPDATE A TABLE INCASE OF JOINS
-
- Mass update on an extensively used DB2 table
by letmelive » Thu Apr 11, 2013 3:55 am - 3 Replies
- 5665 Views
- Last post by pmartyn
Wed Apr 24, 2013 4:03 am
- Mass update on an extensively used DB2 table
-
- Single row update from multiple table
by jvinoth » Wed Sep 24, 2014 9:11 pm - 1 Replies
- 2580 Views
- Last post by NicC
Thu Sep 25, 2014 3:43 am
- Single row update from multiple table