Populate a file with SQL

[arrow483]

I have a file that I need to replace daily with data from an SQL view. Currently I have been doing this by simply running CPYF with REPLACE

CPYF       FROMFILE(GPSVIEWS/ITEMASAVW) +                    
             TOFILE(GPSVIEWS/ITEMASAV1) MBROPT(*REPLACE) +   
             CRTFILE(*YES) FMTOPT(*MAP *DROP)

But now I have to do this while users have read locks on the target, CPYF gags. I tried doing it with SQL

DELETE FROM ITEMASAV1

ok that works, but then I want to repopulate

insert into itemasav1 select * from itemasavw  
ERROR - Statement contains wrong number of values.

Now I know I must be missing something simple here, so what is it ?

I know, I can write an RPG pgm to do it, but won't SQL also do it ?

[jamief]

Re: Populate a file with SQL

that works for me as long as the two tables are identical

INSERT INTO JAMIELIB/SLJRGDS1 SELECT * FROM SLJRGDS2 

when i add a field to one of the files but not the other I get...

Statement contains wrong number of values.               
INSERT INTO JAMIELIB/SLJRGDS1 SELECT * FROM SLJRGDS2 

so then I did this leaving out the one field I added and it worked

INSERT INTO JAMIELIB/SLJRGDS1 SELECT SJRG#, SJSL#, SJORGD, SJPRO#,
SJRGTY, SJCMPD, SJCOST, SJRSTC, SJADDC, SJIS, SJRGAR, SJRGGP,     
SJPO#, SJDTRQST, SJDTRQCOM, SJVOIDDAT, SJVOIDINIT, SJVOIDUSER,    
SJEMP# FROM SLJRGDS2 

Funny how you say!

if you change the name of one of the fields (in this case I changed the leading "S" to "X")
it worked just fine.

[arrow483]

Re: Populate a file with SQL

So any idea why I get

ERROR - Statement contains wrong number of values

I deleted the target file and ran the CPYF to let it re-create, just so I know it has the same fields, definitions, etc.

[jamief]

Re: Populate a file with SQL

i will test with the cpyf and let you know.....go into interactive SQL and prompt for all
fields and then copy that statement out to your code to see if it works when the fields
are listed.

jamie

[jamief]

Re: Populate a file with SQL

sorry! i deleted the file SLJRGDS1 and did cpyf to recreate

CPYF FROMFILE(JAMIELIB/SLJRGDS2) 
     TOFILE(JAMIELIB/SLJRGDS1)   
     CRTFILE(*YES) OUTFMT(*CHAR) 

then did this

INSERT INTO JAMIELIB/SLJRGDS1 SELECT * FROM SLJRGDS2 
2 rows inserted in SLJRGDS1 in JAMIELIB. 

Im not seeing an error......

[B.Hauser]

Re: Populate a file with SQL

Hi,

both files have either a different number of fields or the same fields but in a different sequence.

If so, you have to specify the fields in the insert statement:

Insert Into MyLib/ToFile
   (Field1, Field2, Field3, .... FieldN)  -- Fields in ToFile
   Select FieldA, FieldB, ... FieldX from MyLib/FromFile 

If you are on relase V5R3 or V5R4 you may also use a MQT (Materialized QueryTable). A MQT is a SQL defined table, based on an SQL Select-Statement. Contrary to SQL tables that are created with CREATE TABLE ... AS SELECT ...., the Select-Statement gets stored within the MQT-Object. In this way MQTs can be acutalized by a simple REFRESH TABLE.

Example for an MQT:

CREATE TABLE UmsatzMon AS
      (SELECT Year(VerkDatum) as Jahr, Month(VerkDatum) as Monat, KundeNr, 
              Sum(Wert) as UmsJahr
          from Umsatz
          Group By Year(VerkDatum), Month(VerkDatum), KundeNr)
DATA INITIALLY IMMEDIATE
REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION
MAINTAINED BY USER 

Actualization by executing the SQL Refresh-Statement:

REFRESH TABLE UmsatzMon 

... but MQTs are much more powerful.
For more information just check the following white paper:
Creating and using materialized query tables (MQT) in IBM DB2 for i5/OS


Birgitta

[arrow483]

Re: Populate a file with SQL

Thanks for the suggestions. The situation is this: The view ITEMASAVW is built as a 45 file join. I create the PF from it, and that file is itself joined into about 90 other views. Thus performance is the main reason I don't reference the view directly.

I use MQT's, and while this would seem to be a good candidate, MQT's cannot be referenced by other views. So its the "poor mans' MQT - copy LF to PF.

I have found 2 workarounds that work. If I delete the target, use CPYF to build, then use the DELETE and INSERT, it fails. But if I use CREATE TABLE AS instead of the CPYF, the INSERT works correctly. Why CPYF is not the same, I don.t know.

The result I decided to use should have been obvious to me. Instead of using CPYF with REPLACE, I use SQL DELETE to delete all the records, then use CPYF with *ADD. When *ADD is used, an exclusive lock is not required. This I like better than the INSERT because I can use *MAP *DROP and if the view ever gets a field added before the target PF, it doesn't blow up. BTW, I can't use either ALTER TABLE or CHGPF on the target, both give generic failure messages - I suspect too many SQL views and/or fields involved. So I end up dropping the table and rebuilding everything when that happens.

Thanks for the suggestions ! When I started I figured I had the syntax wrong, but that wasn't the case.