Error in Writer when using a connection string ODBC connection to an iBM iSeries

More
6 years 7 months ago #16306 by bfreiberg
I am build transformations that read data from an IBM iSeries and then write/update to tables on an IBM iSeries. I did my initial PoC using a DSN ODBC connection, but now am converting over to use a non DSN ODBC connection using the connection parameters as produced by the wizard in the create/edit connection screens. I successfully connect, and my reader successfully reads the source data. The writer throws an error as shown below. When I switch back to the DSN ODBC connection everything works fine. Looks like a bug, but I am sure I also could be doing something wrong. Also, is there maybe a better way than using variables in a connection string that anyone has used to be able to dynamically point your transformations at different servers or schemas?

Here is the log:

Information 2/24/2017 8:29:10 AM Starting Transformation...
Information 2/24/2017 8:29:10 AM Map Name: dim_Address_Book POS
Information 2/24/2017 8:29:10 AM Author:
Information 2/24/2017 8:29:10 AM Version:
Information 2/24/2017 8:29:10 AM Description:
Information 2/24/2017 8:29:10 AM Preparing Writers...
Information 2/24/2017 8:29:10 AM Writer: {Writer3}, [IMS.DATAWHS_DV.DIM_ADDRESS_BOOK] Connection Type is: ODBC
Information 2/24/2017 8:29:12 AM Writer: {Writer3}, [IMS.DATAWHS_DV.DIM_ADDRESS_BOOK] Writer { Writer3 } Is Ready
Information 2/24/2017 8:29:12 AM All Writers are Ready
Information 2/24/2017 8:29:12 AM delete from wk_Address_Book

insert into wk_Address_Book
( wk_JDE_Address_Book_Number, wk_Address_Line_1, wk_Address_Line_2,
wk_Address_Line_3, wk_Address_Line_4, wk_Postal_Code, wk_City,
wk_County, wk_State, wk_Country)
select integer(a.alan8), trim(a.aladd1), trim(a.aladd2), trim(a.aladd3), trim(a.aladd4), trim(a.aladdz),
trim(a.alcty1), trim(a.alcoun), trim(a.aladds), trim(a.alctr)
from jaqadta.f0116 a
inner join
(select distinct alan8, max(aleftb) as RecentDate
from jaqadta.f0116
group by alan8) b
on a.alan8 = b.alan8 and
a.aleftb = b.RecentDate
where a.alan8 in (select delta1.aban8
from szqafiles.dwf0101 delta1
where delta1.tg_Action in ('Insert', 'Update') and
date(delta1.tg_Timstmp) >= current date - 2 days)
or a.alan8 in (select delta2.alan8
from szqafiles.dwf0116 delta2
where delta2.tg_Action in ('Insert', 'Update') and
date(delta2.tg_Timstmp) >= current date - 2 days)
Information 2/24/2017 8:29:15 AM Processing Data...
Information 2/24/2017 8:29:15 AM Reader:{Reader} Connection Type is: ODBC
Information 2/24/2017 8:29:15 AM Reader:{Reader} Source Select Statement: Select 'jde_POS' as "Row_Source",
integer(a.aban8) as "jde_Address_Book_Number",
trim(a.abalph) as "Name",
trim(wk_Address_Line_1) as "Address_Line_1",
trim(wk_Address_Line_2) as "Address_Line_2",
trim(wk_Address_Line_3) as "Address_Line_3",
trim(wk_Address_Line_4) as "Address_Line_4",
trim(wk_Postal_Code) as "Postal_Code",
trim(wk_City) as "City",
trim(wk_County) as "County",
trim(wk_State) as "State",
case when wk_Country = ' '
then 'US'
else trim(wk_Country) end as "Country",
case when wk_Country = ' '
then 'USA'
else trim(wk_Country) end as "Country_Full",
trim(a.abat1) as "Search_Type",
trim(a.abac02) as "Client",
sk_dim_Client,
current timestamp as "DW_Timestamp"
from jaqadta.f0101 a
left join wk_Address_Book b on integer(aban8) = wk_JDE_Address_Book_Number
left join dim_Client c on trim(a.abac02) = c.Client
and c.Row_Source = 'jde_POS'
where a.aban8 in (select delta1.aban8
from szqafiles.dwf0101 delta1
where delta1.tg_Action in ('Insert', 'Update') and
date(delta1.tg_Timstmp) >= current date - 2 days)
or a.aban8 in (select delta2.alan8
from szqafiles.dwf0116 delta2
where delta2.tg_Action in ('Insert', 'Update') and
date(delta2.tg_Timstmp) >= current date - 2 days)
Information 2/24/2017 8:29:16 AM Reader:{Reader} Using Bulk Fetch
Error 2/24/2017 8:29:24 AM 1 Record/Line:1 Record Rejected: Field: ROW_SOURCE Value: jde_POS Datatype: VARCHAR Message: Access violation at address 0000000000412E8C in module 'AdvancedETLPro.exe'. Read of address 000007FF2F140028
Error 2/24/2017 8:30:26 AM 2 Record/Line:1 Record Rejected: Field: ROW_SOURCE Value: jde_POS Datatype: VARCHAR Message: Access violation at address 0000000000412E8C in module 'AdvancedETLPro.exe'. Read of address 000007FF2F140028
Error 2/24/2017 8:31:09 AM 3 Record/Line:1 Record Rejected: Field: ROW_SOURCE Value: jde_POS Datatype: VARCHAR Message: Access violation at address 0000000000412E8C in module 'AdvancedETLPro.exe'. Read of address 000007FF2F140028
Error 2/24/2017 8:31:50 AM 4 Record/Line:1 Record Rejected: Field: ROW_SOURCE Value: jde_POS Datatype: VARCHAR Message: Access violation at address 0000000000412E8C in module 'AdvancedETLPro.exe'. Read of address 000007FF2F140028
Error 2/24/2017 8:32:40 AM 5 Record/Line:1 Record Rejected: Field: ROW_SOURCE Value: jde_POS Datatype: VARCHAR Message: Access violation at address 0000000000412E8C in module 'AdvancedETLPro.exe'. Read of address 000007FF2F140028
Error 2/24/2017 8:33:23 AM 6 Record/Line:1 Record Rejected: Field: ROW_SOURCE Value: jde_POS Datatype: VARCHAR Message: Access violation at address 0000000000412E8C in module 'AdvancedETLPro.exe'. Read of address 000007FF2F140028
Error 2/24/2017 8:34:08 AM 7 Record/Line:1 Record Rejected: Field: ROW_SOURCE Value: jde_POS Datatype: VARCHAR Message: Access violation at address 0000000000412E8C in module 'AdvancedETLPro.exe'. Read of address 000007FF2F140028
Error 2/24/2017 8:34:50 AM 8 Record/Line:1 Record Rejected: Field: ROW_SOURCE Value: jde_POS Datatype: VARCHAR Message: Access violation at address 0000000000412E8C in module 'AdvancedETLPro.exe'. Read of address 000007FF2F140028
Error 2/24/2017 8:35:32 AM 9 Record/Line:1 Record Rejected: Field: ROW_SOURCE Value: jde_POS Datatype: VARCHAR Message: Access violation at address 0000000000412E8C in module 'AdvancedETLPro.exe'. Read of address 000007FF2F140028
Error 2/24/2017 8:36:14 AM 10 Record/Line:1 Record Rejected: Field: ROW_SOURCE Value: jde_POS Datatype: VARCHAR Message: Access violation at address 0000000000412E8C in module 'AdvancedETLPro.exe'. Read of address 000007FF2F140028
Information 2/24/2017 8:36:14 AM Reader:{Reader} Read: 10 Records/Lines/Messages (in total)
Information 2/24/2017 8:36:14 AM Reader:{Reader} Records/Lines/Messages per second: 0
Information 2/24/2017 8:36:14 AM Reader:{Reader} Rejected: 10 Record(s)
Information 2/24/2017 8:36:14 AM Reader:{Reader} Time Taken: 00:06:58
Information 2/24/2017 8:36:14 AM Executing SQl after : update dim_Address_Book c
set c.Country_Full = (select trim(a.drdl01)
from jaqacom.f0005 a
where a.drsy = '00' and
a.drrt = 'CN' and
a.drdl01 > '' and
trim(c.Country) = trim(a.drky))
where c.Country in (select trim(b.drky)
from jaqacom.f0005 b
where b.drsy = '00' and
b.drrt = 'CN') and
c.Row_Source = 'jde_POS' and
(c.JDE_Address_Book_Number in (select delta1.aban8
from szqafiles.dwf0101 delta1
where delta1.tg_Action in ('Insert', 'Update') and
date(delta1.tg_Timstmp) >= current date - 2 days) or
c.JDE_Address_Book_Number in (select delta2.alan8
from szqafiles.dwf0116 delta2
where delta2.tg_Action in ('Insert', 'Update') and
date(delta2.tg_Timstmp) >= current date - 2 days))

update dim_Address_Book b
set b.Search_Type_Alpha = (select trim(a.drdl01)
from jaqacom.f0005 a
where a.drsy = '01' and
a.drrt = 'ST' and
trim(b.Search_Type) = trim(a.drky))
where b.Row_Source = 'jde_POS' and
(b.JDE_Address_Book_Number in (select delta1.aban8
from szqafiles.dwf0101 delta1
where delta1.tg_Action in ('Insert', 'Update') and
date(delta1.tg_Timstmp) >= current date - 2 days) or
b.JDE_Address_Book_Number in (select delta2.alan8
from szqafiles.dwf0116 delta2
where delta2.tg_Action in ('Insert', 'Update') and
date(delta2.tg_Timstmp) >= current date - 2 days))
Information 2/24/2017 8:36:14 AM Writer: {Writer3}, [IMS.DATAWHS_DV.DIM_ADDRESS_BOOK] Created : 0 Record(s)
Information 2/24/2017 8:36:14 AM Writer: {Writer3}, [IMS.DATAWHS_DV.DIM_ADDRESS_BOOK] Updated : 0 Record(s)
Information 2/24/2017 8:36:14 AM Writer: {Writer3}, [IMS.DATAWHS_DV.DIM_ADDRESS_BOOK] Deleted : 0 Record(s)
Information 2/24/2017 8:36:14 AM Writer: {Writer3}, [IMS.DATAWHS_DV.DIM_ADDRESS_BOOK] Errors : 10
Information 2/24/2017 8:36:22 AM Transformation Completed

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

More
6 years 7 months ago #16307 by admin
Please post here connection string you used.
You can repalce password with PASSWORD string

Mike
ETL Architect

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

More
6 years 7 months ago #16308 by bfreiberg
Here you go.


DRIVER=iSeries Access ODBC Driver;SYSTEM=PLATO;UID=DATAWHSBLD;PASSWORD=xxxxxxxx;DBQ=datawhs_dv datawhs_dv;DFTPKGLIB=DATAWHS_DV;LANGUAGEID=ENU;PKG=DATAWHS_DV/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;BLOCKSIZE=2048;TRANSLATE=1;REMARKS=1;SIGNON=1;

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

More
6 years 7 months ago - 6 years 7 months ago #16311 by admin
That looks incorrect to me,
"datawhs_dv" is duplicated

DBQ=datawhs_dv datawhs_dv

Mike
ETL Architect
Last edit: 6 years 7 months ago by admin.

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

More
6 years 7 months ago #16313 by bfreiberg
The first instance is for default schema, the second is for the default library list. I took the second one out and I get the same error.

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

More
6 years 7 months ago #16315 by Peter.Jonson
You might have different parameters for the DSN you've created in the registry comparing to connection string


Peter Jonson
ETL Developer
Attachments:

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