Page 1 of 2

update table with more records

PostPosted: Tue Jan 22, 2013 8:56 pm
by pulcinella
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

Re: update table with more records

PostPosted: Tue Jan 22, 2013 9:03 pm
by Akatsukami
:? This is really not comprehensible. I recommend that you recast your requirement in better English.

Re: update table with more records

PostPosted: Tue Jan 22, 2013 9:39 pm
by dick scherrer
Hello,

And show some sample input data (table and file) and the output table content you want when your process is run.

Re: update table with more records

PostPosted: Wed Jan 23, 2013 10:51 pm
by pulcinella
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?

Re: update table with more records

PostPosted: Thu Jan 24, 2013 12:55 am
by dick scherrer
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.

Re: update table with more records

PostPosted: Thu Jan 24, 2013 1:18 am
by Akatsukami
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?

Re: update table with more records

PostPosted: Thu Jan 24, 2013 2:37 am
by Akatsukami
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.

Re: update table with more records

PostPosted: Thu Jan 24, 2013 10:42 pm
by dick scherrer
Hello,

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

d

Re: update table with more records

PostPosted: Thu Jan 24, 2013 11:16 pm
by Akatsukami
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

Re: update table with more records

PostPosted: Fri Jan 25, 2013 1:09 am
by dick scherrer
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). . .?