update table with more records



IBM's flagship relational database management system

update table with more records

Postby pulcinella » Tue Jan 22, 2013 8:56 pm

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
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

 

Re: update table with more records

Postby 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
User avatar
Akatsukami
Global moderator
 
Posts: 1053
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

Postby 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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: update table with more records

Postby 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?
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

Postby 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"?
and I want to update only 3 values (for example):
How do you know whi9ch rows? How would we know?

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.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: update table with more records

Postby 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
User avatar
Akatsukami
Global moderator
 
Posts: 1053
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

Postby 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 :mrgreen: ), 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
User avatar
Akatsukami
Global moderator
 
Posts: 1053
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

Postby dick scherrer » Thu Jan 24, 2013 10:42 pm

Hello,

And i still do not understand the "requirement" . . . . :?

d
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: update table with more records

Postby 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... :D

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

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
"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
User avatar
Akatsukami
Global moderator
 
Posts: 1053
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

Postby 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). . .?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post