PDA

View Full Version : no content is AS/400 table while record count = 196


begunrom
12-18-2006, 11:42 AM
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
12-19-2006, 07:23 AM
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
01-08-2007, 10:53 AM
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
01-10-2007, 04:43 AM
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
01-19-2007, 04:26 PM
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/systems/support/i/databases/index.html

Good luck,
Jens
chris.nickel
01-26-2007, 12:11 PM
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
01-28-2007, 03:10 PM
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
01-29-2007, 01:12 PM
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
01-29-2007, 02:08 PM
URL = jdbc:as400://mycompany.dyndns.org;libraries=OLYFSEC

Regards,
Sven
jbleuel
01-30-2007, 03:35 PM
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
chris.nickel
01-30-2007, 06:51 PM
thx for your replies, but i am getting nervous: i tried uppercase, lowercase, no improvement...
i can not see anything in the logs.
the URL (/OLYFREP) is generated automatically when i input OLYFSEC in the database field of the wizard or parameters window. I dont think i can change it. I tried to change it to ;librairies=OLYFREP in the features list windows but i doesnt seem to take my modif into account
Someone to help with this damned AS400?
thx
jbleuel
01-31-2007, 10:32 AM
Check the security level with the AS/400 admin: 0, 1 or 2?
check the log with (if I remember correctly) use DSPLOG

Seems you do not use the deafult AS/400 driver with Kettle...
Why not use the default AS/400 driver with Kettle, that works fine for me since years.

Cheers,
Jens
didin19
01-31-2007, 11:42 AM
Chris,

I currently use Kettle 2.4.0 with a multi-database environment (Oracle and As/400 db). What I can tell you is Kettle rocks !.

If haven't noticed any connexion problem with my AS/400.

I recommend you to use a good java Sql client (such as Squirrel Sql) in order to get another way to test your connection settings with the same driver you intend to use with Kettle (I hope it is jt400 !).

Regards
Thierry
jbleuel
02-01-2007, 01:16 PM
> dyndns.org
You're going over the public network, aren't you?
Check the firewall ports needed. As I remember Client Access needs more then one port to communicate, may be it is an issue here.
Good luck,
Jens