Darwin-IT: Oracle Inserts based on DB2 selects

It's been a while that I wrote an article. This week I struggled with creating insert scripts based on data from DB2 to be used in my local test database (Oracle XE) at my customer.

We use Siebel and have to integrate here and there by querying data from the Siebel DB2 database. It turns out that my local database adapter has trouble with connecting to the DB2 database. Could not find out what's wrong, so I decided that I would query the data from the Siebel tables and insert it into my local XE database. It's faster anyway (in my case) and it also allows me to manipulate the data for test-case purposes.
But querying db2 to generate insert statements isn't as obvious as I would do it in Oracle.

Here is an example script.

select
'Insert into CONTACT (PARTYROWID,KLANTID,KLANTTYPE,AANGEMAAKTOP,BANKCODE,BANKLOCATIE,KLANTSTATUS,CORRESPONSDENTIETAAL,PRIMAIRTELEFOONNUMMER,'
|| 'PRIMAIRTELEFOONTYPE,PRIMAIREMAIL,PRIMAIREMAILFORMAAT,TELEFOONPRIVE,TELEFOONOVERIG,TELEFOONMOBIEL,TELEFOONWERK,FAX,EMAIL,EMAILFORMAAT,EMAILDATUM,'
|| 'EMAILBRON,INGEZETENEVAN,NATIONALITEIT,ACHTERNAAM,VOLLEDIGEACHTERNAAM,ROEPNAAM,GESLACHTSNAAM,VOORLETTERS,VOLLEDIGEVOORNAMEN,ACADEMISCHETITEL'
||',TUSSENTITEL,ACHTERVOEGSEL,ACHTERTITEL,VOORVOEGSEL,VOORVOEGSELGESLACHTSNAAM,GEBOORTDATUM,GESLACHT,GEBOORTELAND,GEBOORTEPLAATS,EIGENHUIS,FAILLIET,SAMENLEVINGSVORM'
||',BURGERLIJKSTAAT,HUW_VOORWAARDEN,PERSONEEL,TYPEKLANT,MAATSCHAPPELIJKESTATUS,LOKALEKLANTINDELING,CENTRALEKLANTINDELING,KLANTINDELING,LOKAALINGEDEELD,BEHOEFTEPROFIEL,'
||'TAXIDENTIFICATIONNR,WOONPLAATSVERKLARING,SOFINUMMER,REDENGEENSOFINUMER,DATUMOVERLIJDEN,OVERLEDEN,AARDIDENTIFICATIEDOC,DATUMLEGITIMATIE,NRIDENTIFICATIEDOC,'
|| 'DATUMUITGIFTE,LANDUITGIFTE,PLAASTUITGIFTE,PERTELEFOONBENADEREN,PEREMAILBENADEREN,PERPOSTBENADEREN,PERSMSBENADEREN,PERFAXBENADEREN,INSOLVENCYSTATUS,IKBNUMBER)'
||' values ('
|| ''''|| coalesce(ctt.PARTYROWID,'') ||''','
|| ''''|| coalesce(ctt.KLANTID,'') ||''','
|| ''''|| coalesce(ctt.KLANTTYPE,'') ||''','
|| case when ctt.AANGEMAAKTOP is not null then 'to_date('''||varchar_format( ctt.AANGEMAAKTOP,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce(ctt.BANKCODE,'') ||''','
|| ''''|| coalesce(ctt.BANKLOCATIE,'') ||''','
|| ''''|| coalesce(ctt.KLANTSTATUS,'') ||''','
|| ''''|| coalesce(ctt.CORRESPONSDENTIETAAL,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIRTELEFOONNUMMER,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIRTELEFOONTYPE,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIREMAIL,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIREMAILFORMAAT,'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONPRIVE),'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONOVERIG),'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONMOBIEL),'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONWERK),'') ||''','
|| ''''|| coalesce(varchar(ctt.FAX),'') ||''','
|| ''''|| coalesce(varchar(ctt.EMAIL),'') ||''','
|| ''''|| coalesce(varchar(ctt.EMAILFORMAAT),'') ||''','
|| case when ctt.EMAILDATUM is not null then 'to_date('''||varchar_format( ctt.EMAILDATUM,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce(EMAILBRON,'') ||''','
|| ''''|| coalesce(ctt.INGEZETENEVAN,'') ||''','
|| ''''|| coalesce(ctt.NATIONALITEIT,'') ||''','
|| ''''|| coalesce(ctt.ACHTERNAAM,'') ||''','
|| ''''|| coalesce(ctt.VOLLEDIGEACHTERNAAM,'') ||''','
|| ''''|| coalesce(ctt.ROEPNAAM,'') ||''','
|| ''''|| coalesce(ctt.GESLACHTSNAAM,'') ||''','
|| ''''|| coalesce(ctt.VOORLETTERS,'') ||''','
|| ''''|| coalesce(ctt.VOLLEDIGEVOORNAMEN,'') ||''','
|| ''''|| coalesce(ctt.ACADEMISCHETITEL,'') ||''','
|| ''''|| coalesce(ctt.TUSSENTITEL,'') ||''','
|| ''''|| coalesce(ctt.ACHTERVOEGSEL,'') ||''','
|| ''''|| coalesce(ctt.ACHTERTITEL,'') ||''','
|| ''''|| coalesce(ctt.VOORVOEGSEL,'') ||''','
|| ''''|| coalesce(ctt.VOORVOEGSELGESLACHTSNAAM,'') ||''','
|| case when ctt.GEBOORTDATUM is not null then 'to_date('''||varchar_format( ctt.GEBOORTDATUM,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce(ctt.GESLACHT,'') ||''','
|| ''''|| coalesce(ctt.GEBOORTELAND,'') ||''','
|| ''''|| coalesce(ctt.GEBOORTEPLAATS,'') ||''','
|| ''''|| coalesce(ctt.EIGENHUIS,'') ||''','
|| ''''|| coalesce(ctt.FAILLIET,'') ||''','
|| ''''|| coalesce(ctt.SAMENLEVINGSVORM,'') ||''','
|| ''''|| coalesce( ctt.BURGERLIJKSTAAT,'') ||''','
|| ''''|| coalesce( ctt.HUW_VOORWAARDEN,'') ||''','
|| ''''|| coalesce( ctt.PERSONEEL,'') ||''','
|| ''''|| coalesce( ctt.TYPEKLANT,'') ||''','
|| ''''|| coalesce( ctt.MAATSCHAPPELIJKESTATUS,'') ||''','
|| ''''|| coalesce( ctt.LOKALEKLANTINDELING,'') ||''','
|| ''''|| coalesce( ctt.CENTRALEKLANTINDELING,'') ||''','
|| ''''|| coalesce( ctt.KLANTINDELING,'') ||''','
|| ''''|| coalesce( ctt.LOKAALINGEDEELD,'') ||''','
|| ''''|| coalesce( ctt.BEHOEFTEPROFIEL,'') ||''','
|| ''''|| coalesce( ctt.TAXIDENTIFICATIONNR,'') ||''','
|| ''''|| coalesce( ctt.WOONPLAATSVERKLARING,'') ||''','
|| ''''|| coalesce( ctt.SOFINUMMER,'') ||''','
|| ''''|| coalesce( ctt.REDENGEENSOFINUMER,'') ||''','
|| case when ctt.DATUMOVERLIJDEN is not null then 'to_date('''||varchar_format( ctt.DATUMOVERLIJDEN,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce( ctt.OVERLEDEN,'') ||''','
|| ''''|| coalesce( ctt.AARDIDENTIFICATIEDOC,'') ||''','
|| case when ctt.DATUMLEGITIMATIE is not null then 'to_date('''||varchar_format( ctt.DATUMLEGITIMATIE,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce( ctt.NRIDENTIFICATIEDOC,'') ||''','
|| case when ctt.DATUMUITGIFTE is not null then 'to_date('''||varchar_format( ctt.DATUMUITGIFTE,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce( ctt.LANDUITGIFTE,'') ||''','
|| ''''|| coalesce( ctt.PLAASTUITGIFTE,'') ||''','
|| ''''|| coalesce( ctt.PERTELEFOONBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PEREMAILBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PERPOSTBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PERSMSBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PERFAXBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.INSOLVENCYSTATUS,'') ||''','
|| ''''|| coalesce( ctt.IKBNUMBER,'') ||''');'
from siebel.contact ctt
where klantid='12345';

The first 'not so obvious' is the NVL-function. This is a typical Oracle function. For most purposes this can be translated to the coalesce function above. In most cases when the column is empty I want to have an "empty value". In some cases just
giving "coalesce( column-reference,'')" does not suffice. I had to cast the column explicitly to char with the varchar() function:

coalesce(varchar(ctt.TELEFOONPRIVE),'')

Here TELEFOONPRIVE is apparently a number column. The function coalesce() assumes the number datatype and can't accept an empty string as default string.

For dates it is a little more complicated. If there is a date I want to transform it to an Oracle to_date function. But then I have to be sure that the format comming from DB2 is of a standard format. I choose "YYYY-MM-DD HH24:MI:SS". If the date is empty I just want to return an empty string again. I couldn't come up with a simple construct using coalesce(). So I used the CASE WHEN-construct:

case when ctt.DATUMOVERLIJDEN is not null then 'to_date('''||varchar_format( ctt.DATUMOVERLIJDEN,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end

It took me a while to find out that where in Oracle you can provide a date-format to the to_char(<date-value>, <date-format>) function, in DB2 you need the varchar_format(<date-value>, <date-format>) function for that. Luckily the accepted date-formats are the same in my case. So here I transform the date-value from DB2 to the required date-format and concatenate it with the Oracle to_date() function with the same format.

The generated insert statement(s) will look like (enter at values clause added manually for readability):

Insert into CONTACT (PARTYROWID,KLANTID,KLANTTYPE,AANGEMAAKTOP,BANKCODE,BANKLOCATIE,KLANTSTATUS,CORRESPONSDENTIETAAL,PRIMAIRTELEFOONNUMMER,PRIMAIRTELEFOONTYPE,PRIMAIREMAIL,PRIMAIREMAILFORMAAT,TELEFOONPRIVE,TELEFOONOVERIG,TELEFOONMOBIEL,TELEFOONWERK,FAX,EMAIL,EMAILFORMAAT,EMAILDATUM,EMAILBRON,INGEZETENEVAN,NATIONALITEIT,ACHTERNAAM,VOLLEDIGEACHTERNAAM,ROEPNAAM,GESLACHTSNAAM,VOORLETTERS,VOLLEDIGEVOORNAMEN,ACADEMISCHETITEL,TUSSENTITEL,ACHTERVOEGSEL,ACHTERTITEL,VOORVOEGSEL,VOORVOEGSELGESLACHTSNAAM,GEBOORTDATUM,GESLACHT,GEBOORTELAND,GEBOORTEPLAATS,EIGENHUIS,FAILLIET,SAMENLEVINGSVORM,BURGERLIJKSTAAT,HUW_VOORWAARDEN,PERSONEEL,TYPEKLANT,MAATSCHAPPELIJKESTATUS,LOKALEKLANTINDELING,CENTRALEKLANTINDELING,KLANTINDELING,LOKAALINGEDEELD,BEHOEFTEPROFIEL,TAXIDENTIFICATIONNR,WOONPLAATSVERKLARING,SOFINUMMER,REDENGEENSOFINUMER,DATUMOVERLIJDEN,OVERLEDEN,AARDIDENTIFICATIEDOC,DATUMLEGITIMATIE,NRIDENTIFICATIEDOC,DATUMUITGIFTE,LANDUITGIFTE,PLAASTUITGIFTE,PERTELEFOONBENADEREN,PEREMAILBENADEREN,PERPOSTBENADEREN,PERSMSBENADEREN,PERFAXBENADEREN,INSOLVENCYSTATUS,IKBNUMBER) 
values ('1-100BM-100','000000105727750','Person',to_date('2006-05-09 19:59:21','YYYY-MM-DD HH24:MI:SS'),'3365','336515','C','NL','','','','','','','','','','','',to_date('2008-09-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),'FULFILMENT','01','',to_date('2009-07-08 00:00:00','YYYY-MM-DD HH24:MI:SS'),'NL','NL','Name','Name','','Name','I.R.S.','Iris Ronald Simon','','','','','','',to_date('1966-11-11 00:00:00','YYYY-MM-DD HH24:MI:SS'),'M','NL','Tool Town','Y','N','3','1','9','03','01','08','','1','1','Y','','','X','123456789','',null,'N','03',to_date('2005-07-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),'IC4631943',to_date('2004-07-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),'NL','Tool Village','N','N','Y','Y','N','','1-22A-3344');

Wednesday, 16 June 2010

Oracle Inserts based on DB2 selects

No comments:

Post a Comment