SQL Insert Problem

[FKolmann@xxxxxxxxxxxx]

Eric ,Thanks for the  reply.
PTF's are Operations area and the next lot are not due till Aug.
Anyway I have no clue which PTF's to apply.
BTW I have more detail to this saga.
The files I am transferring data to are actually a MS/ACCESS database.
The application runs on a NT box, and via the magic of OBDC and
'LINKED' files MS/ACCESS is using the AS/400 as its disk drive.
Anyway  the MS/ACCESS files  are automatically created by 
the LINK process and use a variety of datatypes, inculding INT, FLT
DBLFLT VCHAR etc etc.  Some MS/ACCESS datatypes do not have
corresponding AS400 datatypes and hence we cannot use them,
eg.  'Logical' and 'Note', we replace them with CHAR.
BTW I found that the simplest way to load these datatypes was to
use SQL.  It automatically maps numerics and char to vchar.
ALSO some files in the MS/ACCES db had over 2million records
and  I needed to check for ADD/CHG/DELS from either the NT
or AS400 applications and refresh the MS/ACCESS data.
This needs to be done as part of day/end processing and cannot 
take tooLong as day/end is long enough as it is.
My first attempt used and RPGIV ILE  pgm to read the AS400 data and 
SQL to get the NT data and compare them.
(I know MR processing from way back and ashamedly admit to being
an expert RPG cycler, but was determined NOT to use MR.)
The process of using SQL single record INSERTs UPDATEs etc was
impossibly  too long (18hrs+)with many confusing issues.
I made sure that the appropriate indexexs (Lgls) existed so that SQL
did not recreate Access Plans from scratch.
I then rewrote the SQL to do Blocked INSERTS via a MODS and hit 
The 16M limit on the size of a MODS. 
To fix bug below I did a work around so the program loaded the 
MODS once processed the MODS and ended.  Then I called the program
repeatedly until all the records were processed passing a PARM
with RRN I was up to.
This cut the time needed to 75 mins.
I then used OVRDBF NBRRCDS and SEQONLY to block the AS400
Reads, time dropped to 40 mins.
I noticed that to be able to update the changed records I needed to 
read EVERY record as UPDATE, so I changed the process to read the
AS400 files as input only and had a second file OPD for the update
as needed.
Time dropped to 30 mins.
This got me thinking how to achieve this result with SQL.
I treated the process a 3 separate steps.
1.   Add new records to the MS db
2.   Chg changed records on the MS db
3.  Remove the records on the MS DB that are no more on AS400

Steps 1 and 3 are single SQL statements that replace entire blocks
of  RPG code.
Step 2  I used a joined SQL that detects the changes so I only
Process the records I need to change.
Time dropped  to 12 mins. processing 2,300,000 rcds.
The lines of code needed is less that 0.25 of the original code.
I noticed SQL is now doing blocking, hashing and all sorts of
esoteric stuff that I was trying to code

I summary the more things change the more they stay the same.
In the past I would have processed sorted versions of the data in
blocked arrival sequence to minimise processing time.
The same rules still apply, block the db I/Os and process only
the records that need to change, BUT SQL does it automatically.
I am learning to use SQL at a higher level than mere record I/O 
as I would with chain etc and I simply need to ensure that the
 necessary Indexes exist to support my objectives.
I am no longer doing SQL BLOCKED inserts as SQL is automatically
choosing to to blocking.
The trick is in not asking too much of SQL, but still knowing
how to process the data in a way that allows blocking etc.
This is slso a reply to my previous questions on how to update
A MS/ACCES db.
It is not as detailed as I would like but maybe it will help
others.
Frank Kolmann


 > Frank,
 >First the standard query: Have you loaded the database group PTFs for this
 >box? You may have issues with known problems that have been fixed. Next
 >question: have you tried writing this in RPGIV? I know I've used the varying
 >character fields without problem, though I know nothing about using float
 >types. Your problem may be specific to SQL.

>   Eric DeLong
>> - -----Original Message-----
>>
>>I loaded a MODS (20000 Ocur) and did an Insert
>> with 20000 ROWS, reload the MODS etc.
>> For the first 20000 records this worked great,
>> took about 30secs BUT then the IOs from the 
>> Input file slowed dramatically to about 5 reads
>> per second, again impossibly long job.

__________________________________________________________________
Get your own FREE, personal Netscape Webmail account today at 
http://webmail.netscape.com/
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---