no content is AS/400 table while record count = 196

[begunrom]

I used the copy table wizatd to make a kettle transformation to copy the contents of a table on AS/400 to a Mysql database.

This usually works fine, however this time with a table called ttccom001700 i get this strange result.
select * from TTCCOM100700
returns 0 records
select count(*) from TTCCOM100700 returns 196 records.

The get SQL select button is able to give me all table fields

The log file does not give any error.

How do i start debugging this?

Version 2.3.0 & 2.3.1

logging :
2006/12/18 17:42:45 - employees - Looking for the transformation [employees] in directory [/reporting]
2006/12/18 17:42:45 - employees - Loading transformation [employees] from repository...
2006/12/18 17:42:45 - employees - Loading step with ID: 360142
2006/12/18 17:42:45 - employees - Loading step with ID: 360143
2006/12/18 17:42:45 - employees - ID_DIRECTORY=10001
2006/12/18 17:42:45 - employees - Loaded the transformation [employees] , directory == null : false
2006/12/18 17:42:45 - employees - Loaded the transformation [employees] in directory /reporting
2006/12/18 17:42:45 - Spoon - Transformation opened.
2006/12/18 17:42:45 - Spoon - Launching transformation [employees]...
2006/12/18 17:42:45 - Spoon - ----> Async exec of prepare and run threads...
2006/12/18 17:42:45 - Spoon - ----> Async exec of prepare and run threads started!!!
2006/12/18 17:42:45 - Spoon - Started the transformation execution.
2006/12/18 17:42:45 - employees - Dispatching started for transformation [employees]
2006/12/18 17:42:45 - employees - Nr of arguments detected:10
2006/12/18 17:42:45 - employees - This is not a replay transformation
2006/12/18 17:42:45 - employees - I found 2 different steps to launch.
2006/12/18 17:42:45 - employees - Allocating rowsets...
2006/12/18 17:42:45 - employees - Allocating rowsets for step 0 --> read from [TTCCOM001700]
2006/12/18 17:42:45 - employees - prevcopies = 1, nextcopies=1
2006/12/18 17:42:45 - employees - Transformation allocated new rowset [read from [TTCCOM001700].0 - write to [TTCCOM001700].0]
2006/12/18 17:42:45 - employees - Allocated 1 rowsets for step 0 --> read from [TTCCOM001700]
2006/12/18 17:42:45 - employees - Allocating rowsets for step 1 --> write to [TTCCOM001700]
2006/12/18 17:42:45 - employees - Allocated 1 rowsets for step 1 --> write to [TTCCOM001700]
2006/12/18 17:42:45 - employees - Allocating Steps & StepData...
2006/12/18 17:42:45 - employees - Transformation is about to allocate step [read from [TTCCOM001700]] of type [TableInput]
2006/12/18 17:42:45 - employees - Step has nrcopies=1
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - distribution activated
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - Starting allocation of buffers & new threads...
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - Step info: nrinput=0 nroutput=1
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - output rel. is 1:1
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - Found output rowset [read from [TTCCOM001700].0 - write to [TTCCOM001700].0]
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - Finished dispatching
2006/12/18 17:42:45 - employees - Transformation has allocated a new step: [read from [TTCCOM001700]].0
2006/12/18 17:42:45 - employees - Transformation is about to allocate step [write to [TTCCOM001700]] of type [TableOutput]
2006/12/18 17:42:45 - employees - Step has nrcopies=1
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - distribution activated
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - Starting allocation of buffers & new threads...
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - Step info: nrinput=1 nroutput=0
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - Got previous step from [write to [TTCCOM001700]] #0 --> read from [TTCCOM001700]
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - input rel is 1:1
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - Found input rowset [read from [TTCCOM001700].0 - write to [TTCCOM001700].0]
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - Finished dispatching
2006/12/18 17:42:45 - employees - Transformation has allocated a new step: [write to [TTCCOM001700]].0
2006/12/18 17:42:45 - employees - This transformation can be replayed with replay date: 2006/12/18 17:42:45
2006/12/18 17:42:45 - employees - Initialising 2 steps...
2006/12/18 17:42:45 - beas400_baan - New database connection defined
2006/12/18 17:42:45 - reporting - New database connection defined
2006/12/18 17:42:45 - reporting - Connected to database.
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - Connected to database [reporting] (commit=200)
2006/12/18 17:42:45 - reporting - Auto commit off
2006/12/18 17:42:45 - beas400_baan - Connected to database.
2006/12/18 17:42:45 - beas400_baan - Auto commit off
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - Connected to database...
2006/12/18 17:42:45 - employees - Step [read from [TTCCOM001700].0] initialized flawlessly.
2006/12/18 17:42:45 - employees - Step [write to [TTCCOM001700].0] initialized flawlessly.
2006/12/18 17:42:45 - employees - Transformation has allocated 2 threads and 1 rowsets.
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - Starting to run...
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - Starting to run...
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - Signaling 'output done' to 1 output rowsets.
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - Finished reading query, closing connection.
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - Signaling 'output done' to 0 output rowsets.
2006/12/18 17:42:45 - reporting - Connection to database closed!
2006/12/18 17:42:45 - write to [TTCCOM001700].0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0
2006/12/18 17:42:45 - beas400_baan - Connection to database closed!
2006/12/18 17:42:45 - read from [TTCCOM001700].0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0
2006/12/18 17:42:45 - Spoon - The transformation has finished!!

This is the table layout as proposed by Kettle

CREATE TABLE ttccom001
(
T_EMNO VARCHAR(6)
, T_NAMA VARCHAR(35)
, T_CADR VARCHAR(9)
, T_SEAK VARCHAR(16)
, T_CWOC VARCHAR(6)
, T_WGRT_1 DECIMAL(53)
, T_WGRT_2 DECIMAL(53)
, T_WGRT_3 DECIMAL(53)
, T_CPCP VARCHAR(8)
, T_HWEM DECIMAL(53)
, T_CWTT VARCHAR(3)
, T_CLAN VARCHAR(3)
, T_SDTE DATETIME
, T_EDTE DATETIME
, T_CTRG VARCHAR(3)
, T_RATS DECIMAL(53)
, T_DPWK DECIMAL(53)
, T_HRSD DECIMAL(53)
, T_FINR VARCHAR(9)
, T_DAOB DATETIME
, T_SEXE VARCHAR(1)
, T_CIST VARCHAR(1)
, T_TELW VARCHAR(15)
, T_TLW1 VARCHAR(15)
, T_TEFW VARCHAR(15)
, T_INFO VARCHAR(50)
, T_CCAL VARCHAR(9)
, T_TXTA INT
, T_REFCNTD INT
, T_REFCNTU INT
)
;

[MattCasters]

Hi Gunther, this reeks of driver rot. It's been a long while since we updated the AS/400 driver.
I'll look into this this week.

All the best,

Matt

[jbleuel]

Did you looked in the AS/400 job log? sometimes there are pretty good error messages:
WRKACTJOB, look at Job QZDASOINIT in Subsystem QUSRWRK

I guess it is the Decimal(53) or a AS/400 / iSeries related bug.

Good luck,
Jens

[begunrom]

Jens,

I double checked the job Job QZDASOINIT in Subsystem QUSRWRK , but there is no indication of the problem.

As the table is a standard Baan table i connot do much at the format.

Why do you suspect the Decimal(53) to be the problem?

[jbleuel]

Jens,

I double checked the job Job QZDASOINIT in Subsystem QUSRWRK , but there is no indication of the problem.

As the table is a standard Baan table i connot do much at the format.

Why do you suspect the Decimal(53) to be the problem?

May be the JDBC-driver has problems with a Decimal(53) - it's pretty big, isn't it? May be you could look up the limit for AS/400 or the JDBC driver, but
to figure out where the problem is:
- try to do the select with the iSeries Operations Navigator (select the database, context menu gives something like "run sql queries")
- try to use ODBC
- try to update the AS/400 JDBC driver

Mostly this is related to bugs in the JDBC, so check the APARs for iSeries about JDBC and your release (for all releases I checked it and just got 250 bugs ;-)
http://www-304.ibm.com/jct01004c/sys...ses/index.html

Good luck,
Jens

[chris.nickel]

Hi,
May i reply to this post which is about AS400 connection.
Actually i have troubles having a connection to work with my AS400.
Everytime i push the test button in the connection wizard, i have the message "Sign on request is not valid". Yet i use the right IP and user/pwd (i tested it with telnet). Could you tell me exactly what parameters you use (port,..)
Thx

[aalmero]

Hi,
May i reply to this post which is about AS400 connection.
Actually i have troubles having a connection to work with my AS400.
Everytime i push the test button in the connection wizard, i have the message "Sign on request is not valid". Yet i use the right IP and user/pwd (i tested it with telnet). Could you tell me exactly what parameters you use (port,..)
Thx

just make sure that you have the following constructed
URL = jdbc:as400://[your_host_ip];libraries=[your_libraryname]

click on the "feature list" button to check the value of the URL.

[chris.nickel]

Yes, the URL in the feature list
URL = jdbc:as400://mycompany.dyndns.org/OLYFSEC
OLYFSEC being the library where i have my AS400 files

and Driver class = com.ibm.as400.access.AS400JDBCDriver

i checked the password works when i logon normally
i dont see what could go wrong, but it is really a pain because i have put all my hopes in kettle for my project...

[sboden]

URL = jdbc:as400://mycompany.dyndns.org;libraries=OLYFSEC

Regards,
Sven

[jbleuel]

Chris,
try to write the username & password in upper case. Sometimes there are issues when you use other security levels on AS/400.
May be check also the log with LOG in AS/400 for security issues.
Good luck,
Jens