Originally posted by: PatPather

Hi all

I have constructed a map to write data to a mainframe DB2 database.  When I run the map, I get the message "Target not found".   When using the Database Interface Designer, I selected no under "Access user tables/procedures only".  This allowed me to select the correct table as schemas are varied on our mainframe database, and generate the type tree.

I also defined odbc (system and user) entries to the database.  I am able to insert data to the database from CLIP, or the Command Editor.

I have a similar mdq and map that inserts rows into a DB2 database on the same server on which WTX resides.  This works.

Does anyone have any idea as to what the problem could be?

Thank you

Originally posted by: paul.brett

If the Database Interface Designer can access the database, then the Map Designer should also be able to access the database, if you're using the same MDQ file for both.

Are you using the Map Designer to run the map, or some other execution method?  Are you running the map on the same machine as the Design Studio?

Thank-you.

Paul.

Follow me on Twitter

Originally posted by: PatPather

Hi Paul

The DB Interface Designer can access the database, as I am using this to create the table type tree.  I am using the same MDQ for both.  Yes, WTX and DID are on the same machine and I am using the map designer to run the map.

Thank you

Pat

Originally posted by: paul.brett

Please post your output card settings (screenshot) and a copy of the full trace for a map run.

Thank-you.

Paul.

Follow me on Twitter

Originally posted by: PatPather

Hi Brett

I have attached the files.  Sorry about the slow response.  We only have one server on which WTX is loaded, and some other stuff.  AND there are about 6 people sharing this server.  As we remote in to this server, there can only be 2 people on at any one time.  So I have to take my place in the queue.

Also, as a company, we are newbies to WTX and FTM.  So there is not a lot of talent in this area, and basically no full time people on this!!!

Hopefully, we will be able to change all of this soon!

Thank you for all your help  

Attachment(s)

Paul.Brett.docx   157 KB 1 version

zOS_Insert_MV_TRAN_T_trace.txt   576 KB 1 version

Originally posted by: paul.brett

Perhaps I did not make it clear, but I need a full database trace.  Please add -T to the adapter command string.  It will produce a .DBL file.

Thank-you.

Paul.

Follow me on Twitter

Originally posted by: paul.brett

Thank-you.

Paul.

Follow me on Twitter

Originally posted by: PatPather

Thank you Paul

I see the problem now.  Let me work on this, and I will keep you posted.

Take care

Attachment(s)

Insert_All.dbl   13 KB 1 version

Originally posted by: PatPather

Hi Paul

Still having issues with this field.  I changed the format of the two items in the type tree (CRT_TS and LAST_UPDT_TS) to reflect that of zOS DB2 which is "{CCYY-MM-DD}-{HH24.MM.SS.6-6}".  I mapped these items to the CURRENTDATETIME. I analyzed, built and ran and got the following lines in the DB trace.  Looks pretty whack to me as the format of the data within quotes is '{CCYY-MM-DD} {YY:MM:DD}.{MM.SS.6-6}'.  And the date is being specified as 2015-01-01, not the current date.  Wierd.

        <1896-7636-07/30/15 09:52:18>:    Column 6 CRT_TS : {ts '2015-01-01 00:00:00.52.18.748000'}

        <1896-7636-07/30/15 09:52:18>:    Column 8 LAST_UPDT_TS : {ts '2015-01-01 00:00:00.52.18.748000'}

I have attached the DB trace and the map in XML format.

Thank you

Attachment(s)

zOS_Insert_MV_TRAN_T.xml   17 KB 1 version

Insert_All.dbl   13 KB 1 version

Originally posted by: paul.brett

Please do not alter the Typetree after it has been imported.

I created a test table here:

create table TEST7(
Name   varchar(20),
LastUpdate           timestamp)
go

...and then used the Database Interface Designer to create the Typetree.  The object properties are:

{CCYY-MM-DD}[ {HH24:MM[:SS[.6-6]]}

....as I would expect.  I created a simple insert map, and it ran successfully.

<4200-7496-07/31/15 12:16:03>:    Validating the adapter command...
<4200-7496-07/31/15 12:16:03>:    Database type is DB2
<4200-7176-07/31/15 12:16:03>:    Connecting...
<4200-7176-07/31/15 12:16:03>:    Datasource : DSNV1007
<4200-7176-07/31/15 12:16:03>:    UserID     : pab1
<4200-7176-07/31/15 12:16:03>:    Password   : *****
<4200-7176-07/31/15 12:16:03>:    Context: Output Card, Transaction scope: Map
<4200-7176-07/31/15 12:16:03>:    On Failure: Rollback, On Success: Create
<4200-7176-07/31/15 12:16:03>:    TRACE command specified, file: Y:\DB2\Test1.dbl
<4200-7176-07/31/15 12:16:03>:    Table name: PAB1.TEST7
<4200-7176-07/31/15 12:16:03>:    Connection to datasource DSNV1007 has been established.
<4200-7176-07/31/15 12:16:03>:    Servername/alias:->
<4200-7176-07/31/15 12:16:03>:    DB2
<4200-7176-07/31/15 12:16:03>:    Database  Version/Type:->
<4200-7176-07/31/15 12:16:03>:    10.01.0005
<4200-7176-07/31/15 12:16:03>:    Client Library/environment(ODBC DriverName):->
<4200-7176-07/31/15 12:16:03>:    DB2CLI.DLL
<4200-7176-07/31/15 12:16:03>:    Client Library version (ODBC DriverVersion):->
<4200-7176-07/31/15 12:16:03>:    10.01.0000
<4200-7176-07/31/15 12:16:03>:    Database connect status: rc = 0, count = 1.
<4200-7176-07/31/15 12:16:03>:    Returned status: (0) Success
<4200-7176-07/31/15 12:16:03>:    Interface library version 8.4.1(14)
<4200-7176-07/31/15 12:16:03>:    Map: Y:\DB2\Test1.mmc,  Timestamp: 07/31/15 12:16:03.
<4200-7176-07/31/15 12:16:03>:    Loading data for output card 1.
<4200-7176-07/31/15 12:16:03>:    Database adapter: DB2 Version 8.4.1.3(14)
<4200-7176-07/31/15 12:16:03>:    Starting database load to table PAB1.TEST7...
<4200-7176-07/31/15 12:16:03>:    Datasource : DSNV1007
<4200-7176-07/31/15 12:16:03>:    UserID     : pab1
<4200-7176-07/31/15 12:16:03>:    Password   : *****
<4200-7176-07/31/15 12:16:03>:    Context: Output Card, Transaction scope: Map
<4200-7176-07/31/15 12:16:03>:    On Failure: Rollback, On Success: Create
<4200-7176-07/31/15 12:16:03>:    TRACE command specified, file: Y:\DB2\Test1.dbl
<4200-7176-07/31/15 12:16:03>:    Table name: PAB1.TEST7
<4200-7176-07/31/15 12:16:03>:    Update mode is off.
<4200-7176-07/31/15 12:16:03>:    Database column IDENTITY status: 0.
<4200-7176-07/31/15 12:16:03>:    The columns are of the following types:
<4200-7176-07/31/15 12:16:03>:       Column 1 (NAME) type is VARCHAR, precision is 20.
<4200-7176-07/31/15 12:16:03>:    Database column IDENTITY status: 0.
<4200-7176-07/31/15 12:16:03>:       Column 2 (LASTUPDATE) type is TIMESTAMP, precision is 26.
<4200-7176-07/31/15 12:16:03>:    The insert statement to be executed is:
<4200-7176-07/31/15 12:16:03>:    INSERT INTO PAB1.TEST7  VALUES (?,?)
<4200-7176-07/31/15 12:16:03>:    INSERT INTO PAB1.TEST7  VALUES (?,?)
<4200-7176-07/31/15 12:16:03>:    Driver version is : 03.  Version 1 flag set to 0.
<4200-7176-07/31/15 12:16:04>:    1 rows inserted.
<4200-7176-07/31/15 12:16:04>:    Database load complete.
<4200-7176-07/31/15 12:16:04>:    Returned status: (0) Success
<4200-7176-07/31/15 12:16:04>:    Cleaning up and committing the transaction...
<4200-7176-07/31/15 12:16:04>:    Transaction commit succeeded.
<4200-7176-07/31/15 12:16:04>:    Returned status: (0) Success

I note the error in your DBL:

Error in SQLExecute

Message: [IBM][CLI Driver] CLI0111E  Numeric value out of range. SQLSTATE=22003

SQL State: 22003

....and I think perhaps you're focusing on the wrong column.  This indicates an issue with an INT field.

Thank-you.

Paul.

Follow me on Twitter