Thursday 9 August 2012

Bulk insert into project (all custom tables)

This is a quick work around for a scenario that I have found a number of times.  The predicament I was currently in… (I was on a bus, travelling at 60km/h – no – just kidding)   I needed to copy custom tables from AS/400 to oracle.  But..  I did not have a fat client which had the specs for the custom tables.  F98403 needs the specs, so I was in trouble.  There was about 70 custom tables, so I did not want to manually pop them all into my project for an advanced get (to get the specs to my deployment server).  Oh yeah, my enterprise server was linux too, so there was no ODBC to run the conversions on the ent server… So, I ran the following:

INSERT INTO SY900.F98222 (POOMWPRJID, POOMWOBJID, POOMWOT, POSRCRLS, POOMWUSER, POOMWMKEY, POENHV, POPATHCD, PODATS, POOMWCHS, POOMWOST, POOMWOVS, POOMWOBSDT, POOMWCRTDT, POOMWAC, POOMWTC, POOMWTCV, POOMWPOS1, POOMWPOS2, POOMWPOS3, POOMWPOS4, POOMWPOS5, POOMWPOD1, POOMWPOD2, POOMWPOD3, POOMWPOD4, POOMWPOD5, POOMWPON1, POOMWPON2, POOMWPON3, POOMWPON4, POOMWPON5, POPID, POMKEY, POUSER, POUPMJ, POUPMT, POTIMEZONES, PODSAVNAME)
SELECT 'GenerateTables', siobnm    , 'TBLE' ,'E900','JDE','VSYDJDE02','PY900','PY900',' ','0','01',' ',0,0,' ',' ',' ','0',' ',' ',' ',' ',0,0,0,0,0,0.000000,0.000000,0.000000,0.000000,0.000000,'P98220','DSBSLSMOIRS','MOIRS',104303,143243.000000,' ',' '
FROM ol900.f9860 where siobnm like 'F55%';
commit ;

My project name was GenerateTables.  I was release E900.  Did an advanced get, ran the R98403.  Everyone was happy!

No comments: