Welcome Guest! Log in

Topic-icon Question DB2 AS400 to MSSQL

More
23 Mar 2016 12:29 #1 by letancel
letancel created the topic: DB2 AS400 to MSSQL
Hi,

I'm facing an error with the LOAD Rdbms to Mssql (With BULK) :
I have DB2 AS400 tables that I want to integrate in a Mssql table.
-> When I do inner joins between these tables, I get no worries -> It works.
-> But, when I have left outer ("Left Part" checked in the Properties) joins between the AS400 tables, I get this error :
com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0104] Elément syntaxique ) n'est pas correct. Eléments possibles : ( ? : DAY INF NAN RID ROW RRN CASE CAST CHAR DATE DAYS HASH HOUR.

So, I checked the executed code, and I was surprised to find the (+) character instead of the classic "LEFT OUTER JOIN" :
select
SUBSTRING(MUTTBLHD_601.CONTEN, 1, 25) L1_lib_acte,
MUCPHDHD.MTPURO L2_mnt_base_secu,
MUCPHDHD.NUMLIG L3_num_ligne_decompte,
MUCPHDHD.OBSVRC L4_code_observation,
MUCPHGHD.NUMADH L21_NUMADH
from MUCPHGHD MUCPHGHD, MUCPHDHD MUCPHDHD, MUTTBLHD MUTTBLHD_609, MUTTBLHD MUTTBLHD_601
where (1=1)
AND (MUCPHDHD.OBSVRC = MUTTBLHD_609.IDENTI(+) AND MUTTBLHD_609.NUMTBL(+) = 609 AND MUTTBLHD_609.CDGRPE(+) = 1 AND MUTTBLHD_609.CDSGRP(+) = '01')
AND (MUCPHDHD.REFDEC = MUCPHGHD.REFDEC
and MUCPHDHD.CDORGA = MUCPHGHD.CDORGA
and MUCPHDHD.CDSGRP = MUCPHGHD.CDSGRP)
AND (MUCPHDHD.ACTMUT = MUTTBLHD_601.IDENTI(+) AND MUTTBLHD_601.NUMTBL(+) = 601 AND MUTTBLHD_601.CDGRPE(+) = 1) AND ( MUCPHGHD.REFDEC = '5365NOEMI00001' )


Then, I tried to modify the "Join Mode" to "Implicit" -> Same behaviour and then "Join Mode" to "Explicit" and I got an executed code with no (+), but no LEFT OUTER JOIN too.. :
select
SUBSTRING(MUTTBLHD_601.CONTEN, 1, 25) L1_lib_acte,
MUCPHDHD.MTPURO L2_mnt_base_secu,
MUCPHDHD.NUMLIG L3_num_ligne_decompte,
MUCPHDHD.OBSVRC L4_code_observation,
MUCPHGHD.NUMADH L21_NUMADH
from ((MUCPHDHD MUCPHDHD
MUCPHGHD MUCPHGHD
ON MUCPHDHD.REFDEC = MUCPHGHD.REFDEC
and MUCPHDHD.CDORGA = MUCPHGHD.CDORGA
and MUCPHDHD.CDSGRP = MUCPHGHD.CDSGRP)
MUTTBLHD MUTTBLHD_601
ON MUCPHDHD.ACTMUT = MUTTBLHD_601.IDENTI
AND MUTTBLHD_601.NUMTBL = 601
and MUTTBLHD_601.CDGRPE = 1)
MUTTBLHD MUTTBLHD_609
ON MUCPHDHD.OBSVRC = MUTTBLHD_609.IDENTI
AND MUTTBLHD_609.NUMTBL = 609
and MUTTBLHD_609.CDGRPE = 1
and MUTTBLHD_609.CDSGRP = '01'
where (1=1) AND ( MUCPHGHD.REFDEC = '5365NOEMI00001' )


Could you please help me to fix this out?

Thanks

Please Log in or Create an account to join the conversation.

More
24 Mar 2016 11:26 - 28 Sep 2016 16:12 #2 by Thomas BLETON
Thomas BLETON replied the topic: DB2 AS400 to MSSQL
Hmmm this looks like an issue with your Template version, and/or the AS400 technology definition in your workspace. I can't reproduce this problem.
Can you please send an "Export models and dependencies" of your mapping, to the support team ?
Last Edit: 28 Sep 2016 16:12 by Thomas BLETON.

Please Log in or Create an account to join the conversation.

More
04 Apr 2016 11:50 #3 by letancel
letancel replied the topic: DB2 AS400 to MSSQL
Hi,

Sorry for the late answer.
I tried to export the model and its dependencies, but I have a "Failed to export models" error with a NullPointerException during the export...
Is there another way to export the mapping with its dependencies?

Thanks

Please Log in or Create an account to join the conversation.

More
04 Apr 2016 12:10 - 28 Sep 2016 16:12 #4 by Thomas BLETON
Thomas BLETON replied the topic: DB2 AS400 to MSSQL
This seems to confirm there is something corrupted in your workspace.
Please send a ZIP archive of your workspace directory to the support team, and add a link to this forum topic in your message.
Last Edit: 28 Sep 2016 16:12 by Thomas BLETON.

Please Log in or Create an account to join the conversation.

More
04 Apr 2016 12:35 #5 by letancel
letancel replied the topic: DB2 AS400 to MSSQL
I just sent you an email with my workspace in attachment.
The file is voluminous (25Mo) so please tell me if you don't receive it, i will find another way to send it to you.

Thanks

Please Log in or Create an account to join the conversation.