Not able to extract metadat from the DB2 Query with function

itstushar

• October 31, 2013 00:00

• Answered

Follow

Hi,
I have following query which runs on DB2 perfectly fine. But when i try to extract the metadata i get following error in CloverETL.
Please check the qyuery and let me know if anything is wrong in that or if there is other way to achieve the same let me know.

Thanks!

Metadata extraction failed: [ibm][db2][jcc][10206][10302] Null SQL string passed.

Query :
with baseMachineDetails as (select p.PRG_ID,
basecodeCountry.ID,basecodeCountry.basecode,basecodeCountry.country--,basecodeCountry.countryAvail
from UFXMOCID.PRG_GLB_AVL_CONFIG p,
xmltable('$x/DetailedProgramStructureDTO/BaseMachinesOptionGroup/baseCodes/baseCodeList/GAConfigStateList/GAConfigStateList'
passing cast(p.CONFIG_XML as xml) as "x"
columns ID Integer path '@baseCodeId' ,
country Varchar(4) path 'countryCode',
basecode VARCHAR(15) path 'baseCode',
countryAvail varchar(1) path 'gaConfigStateValue'
) as basecodeCountry
where p.LAST_UPDATE=(select max(last_update) from UFXMOCID.PRg_GLB_AVL_CONFIG p2 where p2.prg_id=p.prg_id)
and basecodeCountry.country='GB'
and basecodeCountry.countryAvail='Y'
),
baseMachinenotes as (
select pg.PRG_ID,
baseNotes.ID,baseNotes.basecode,baseNotes.country,baseNotes.note,baseNotes.seq
from UFXMOCID.PRG_GLB_AVL_CONFIG pg,
xmltable('$x/DetailedProgramStructureDTO/BaseMachinesOptionGroup/baseCodes/baseCodeList/notesList/notesList/GAConfigStateList/GAConfigStateList'
passing cast(pg.CONFIG_XML as xml) as "x"
columns ID Integer path '@baseCodeId' ,
country Varchar(4) path 'countryCode',
basecode VARCHAR(15) path 'baseCode',
note VARCHAR(1500) PATH '../../note',
seq VARCHAR(2) PATH '../../@sequence',
countryAvail varchar(1) path 'gaConfigStateValue'
) as baseNotes
where pg.LAST_UPDATE=(select max(last_update) from UFXMOCID.PRg_GLB_AVL_CONFIG p2 where p2.prg_id=pg.prg_id)
and baseNotes.country='GB'
and baseNotes.countryAvail='Y'
),
requiredOptionGroup as (
select p.PRG_ID,
optionGrp.ID,optionGrp.DESCRIPTION,optionGrp.OPTIONGROUPID
from UFXMOCID.PRG_GLB_AVL_CONFIG p,
xmltable('$x/DetailedProgramStructureDTO/RequiredOptionGroupList/requiredOptionGroupList/prgAsgnConfigStateList/prgAsgnConfigStateList'
passing cast(p.CONFIG_XML as xml) as "x"
columns
ID Integer path '@baseCodeId' ,
DESCRIPTION VARCHAR(1500) path '../../description',
OPTIONGROUPID varchar(15) path '../../optionGroupId',
countryAvail varchar(1) path 'prgAsgnConfigStateValue'
) as optionGrp
where p.LAST_UPDATE=(select max(last_update) from UFXMOCID.PRg_GLB_AVL_CONFIG p2 where p2.prg_id=p.prg_id)
and optionGrp.countryAvail='R'
),
requiredOptionNotes as (
select p.PRG_ID,
basecodeCountry.ID,basecodeCountry.note,basecodeCountry.seq
from UFXMOCID.PRG_GLB_AVL_CONFIG p,
xmltable('$x/DetailedProgramStructureDTO/RequiredOptionGroupList/requiredOptionGroupList/notesList/notesList/prgAsgnConfigStateList/prgAsgnConfigStateList'
passing cast(p.CONFIG_XML as xml) as "x"
columns
ID Integer path '@baseCodeId' ,
note VARCHAR(1500) PATH '../../note',
seq VARCHAR(1500) PATH '../../@sequence',
countryAvail varchar(1) path 'prgAsgnConfigStateValue'
) as basecodeCountry
where p.LAST_UPDATE=(select max(last_update) from UFXMOCID.PRg_GLB_AVL_CONFIG p2 where p2.prg_id=p.prg_id)
and basecodeCountry.countryAvail='Y'
),
requiredOptions as (
select p.PRG_ID,
reqop.ID,reqop.country,reqop.descript,reqop.optioncode,reqop.optionGroupId
from UFXMOCID.PRG_GLB_AVL_CONFIG p,
xmltable('$x/DetailedProgramStructureDTO/RequiredOptionGroupList/requiredOptionGroupList/optionCodeList/optionCodeList/GAConfigStateList/GAConfigStateList'
passing cast(p.CONFIG_XML as xml) as "x"
columns ID Integer path '@baseCodeId' ,
country Varchar(4) path 'countryCode',
descript VARCHAR(1500) path '../../description',
optioncode varchar(10) path '../../optionCode',
optiongroupId varchar(15) path '../../optionGroupId',
countryAvail varchar(1) path 'gaConfigStateValue'
) as reqop
where p.LAST_UPDATE=(select max(last_update) from UFXMOCID.PRg_GLB_AVL_CONFIG p2 where p2.prg_id=p.prg_id)
and reqop.country='GB'
and reqop.countryAvail='Y'
),
requiredOptionCodesNotes as (
select p.PRG_ID,
reqOCNote.ID,reqOCNote.note,reqOCNote.country,reqOCNote.optioncode,reqOCNote.optionGroupId,reqOCNote.seq
from UFXMOCID.PRG_GLB_AVL_CONFIG p,
xmltable('$x/DetailedProgramStructureDTO/RequiredOptionGroupList/requiredOptionGroupList/optionCodeList/optionCodeList/notesList/notesList/GAConfigStateList/GAConfigStateList'
passing cast(p.CONFIG_XML as xml) as "x"
columns ID Integer path '@baseCodeId' ,
country Varchar(4) path 'countryCode',
note VARCHAR(1500) PATH '../../note',
seq VARCHAR(1500) PATH '../../@sequence',
optioncode varchar(10) path '../../../../optionCode',
optiongroupId varchar(3) path '../../../../optionGroupId',
countryAvail varchar(1) path 'gaConfigStateValue'
) as reqOCNote
where p.LAST_UPDATE=(select max(last_update) from UFXMOCID.PRg_GLB_AVL_CONFIG p2 where p2.prg_id=p.prg_id)
and reqOCNote.country='GB'
and reqOCNote.countryAvail='Y'
)
select distinct bm.PRG_ID,bm.ID,bm.basecode,
bm.country,'BASE_NOTE_'||rtrim(bm.BASECODE)||'_'||bn.seq as baseNoteId, bn.note,
rtrim(bm.basecode)||'_ROG'|| cast(bn.prg_id as varchar(5))||'_'||cast(rog.OPTiongroupid as varchar(5)) as ROGOID,rog.description,
rtrim(bm.basecode)||'_ROG'|| cast(bn.prg_id as varchar(5))||'_'||cast(rog.OPTiongroupid as varchar(5))||'_'||ron.seq as reqOGNoteOID,ron.note,
rtrim(bm.basecode)||'_ROG'|| cast(bn.prg_id as varchar(5))||'_'||cast(rog.OPTiongroupid as varchar(5))||'_'||ro.optioncode as optionId,ro.optioncode,ro.descript,
rtrim(bm.basecode)|| cast(bn.prg_id as varchar(5))||'_'||ro.optioncode||'NOTE_'||rocn.seq,rocn.note
from baseMachineDetails bm
left join baseMachinenotes bn on (bm.PRG_ID=bn.prg_id and bm.id=bn.id)
left join requiredOptionGroup rog on (bm.prg_id=rog.prg_id and bm.ID=rog.id)
left join requiredOptionNotes ron on (rog.prg_id=ron.prg_id and rog.id =ron.id)
left join requiredOptions ro on (ro.PRG_ID=rog.prg_id and ro.id= rog.id and rog.OPTIONGROUPID=ro.optionGroupId)
left join requiredOptionCodesNotes rocn on (rocn.prg_id= ro.prg_id and rocn.id=ro.id and rocn.optioncode=ro.optioncode)

• 

  novakj

  • November 01, 2013 08:57

  0

  Comment actions Permalink

  Hi Tushar,
  
  Can you please provide us the run log for more details about the error? We can try to reproduce the issue, but for that we need from you more information about your database schema, e.g. the create statement of your database (or at least affected tables).
  
  Could you please provide these additional information about your issue?
  
  Thank you.
• 

  itstushar

  • November 01, 2013 16:02

  0

  Comment actions Permalink

  This is happening when i am trying to extract metadata. i got the below error message where i can find the logs?
  
  Metadata extraction failed: [ibm][db2][jcc][10206][10302] Null SQL string passed.
• 

  novakj

  • November 04, 2013 15:49

  0

  Comment actions Permalink

  Hi Tushar,
  
  In the Designer you can see the run log of your graph in the Console View. Please can you send us the content of your Console after run of your graph?
  
  Thank you very much.

Please sign in to leave a comment.