IDS Forum

Fw: PMR 40858,422,000

Posted By: Peter_Logan@spartanstores.com
Date: Monday, 8 April 2013, at 10:25 a.m.

IDS 11.70.fc3x3
Aix 6.1

We currently are using Web methods to insert into a text column type via
JDBC. They are now moving to Sterling and doing the same insert they are
getting an -619 error. This has gone through the SI support, and
basically the answer from them is below..

Does anyone have any experience of using SI ... and inserting into a text
column.

thank you for any and all help.

Peter

Peter Logan
Senior Database Administrator
Phone: 616/878-8309
----- Forwarded by Peter Logan/Corporate/Spartan on 04/08/2013 10:18 AM
-----

From: sterling_support@ecurep.ibm.com
To: Bill_Neumeyer@spartanstores.com,
Cc: Geoff_Bernard@spartanstores.com, Peter_Logan@spartanstores.com
Date: 04/05/2013 12:18 PM
Subject: RE: PMR 40858,422,000

Bill

Thanks for all the information. I will review this with my team and get
back to you as soon as I can.

Thanks
Debby

----- History -----
IBM ECuRep Mail Gateway

Date:
Fri, 5 Apr 2013 10:21:01 -0400
Attachments:
To:
sterling_support@ecurep.ibm.com

Cc:
Geoff_Bernard@spartanstores.com, Peter_Logan@spartanstores.com
From:
Bill_Neumeyer@spartanstores.com
Reply-To:
Bill_Neumeyer@spartanstores.com
Subject:
RE: PMR 40858,422,000

Debbie and Pat,

Unfortunately we will not be able to comply with your request to
run the same insert through a LWJDBC.

The reason for this is that Informix is not a database system that
is supported by SI beyond a basic functionality level. The ability to
insert into an CLOB or BLOB fields via a LWJDBC appears to require
advanced function classes that was developed by Sterling. The Informix
TEXT field does not seem to have an equivalent type in any other database
versions.

Please reference Oracle JDBC pool value of:
.varDataClassName=com.sterlingcommerce.woodstock.util.frame.jdbc.OracleVarData.

There are similar VarData classes for MSSQL, Sybase and DB2, but
there don't seem to be any for Informix.

I have pulled in some of my internal support in an attempt to find
a third-party class that we could use but have been unsuccessful. We have
reached the conclusion that to continue working on something unsupported
by SI and with little chance of success would be a waste of effort.

If there is a way to do this in Informix, please let me know how
and I will be happy to attempt it again.

It seems that the first step towards a solution to the situation
would be to develop the class for Informix that serves the same function
as the Oracle, MSSQL, Sybase and DB2 VarData classes.

Then either:

An increase in the number of parameters available in the LWJDBC
service in a way similar to how the DocKeywordReplace service has an
unlimited number of parameters, so that we could potentially do this as a
BP

Add functionality to the Translator and Mapper to support BLOB,
CLOB and TEXT columns.

Thank you,

Bill Neumeyer

From: sterling_support@ecurep.ibm.com
To: Bill_Neumeyer@spartanstores.com
Cc: Geoff_Bernard@spartanstores.com, Peter_Logan@spartanstores.com
Date: 04/02/2013 08:19 AM
Subject: RE: PMR 40858,422,000

Good Morning,

We were able to review the trace log that you sent. I wanted to let you
know that the steps we need you to take with setting up the simple as
layed out by Pat and also why we need them

At this point, our plan of action is to see what the differences are
between the LWJDBC and the Map's insert statements.
Since there is not a way within SI to see the insert, I've discussed
with a level 3 rep in Informix support on how to turn on the trace for
Informix.

This is what you did aleready to send us the trace
To keep this as simple as possible, create a map that connects to the
bill_test that Peter created (judging from the email you forwarded).
Just make sure that you modify the correct pool in the properties file.
Then just have a simple link from a string field on the input to the
bill_test field on the output, your input file can just pass the word
"test" or something simple.
When you run that map, it should create the ifxjdbctrace file, and
contain the insert that informix is receiving.

Now simply do this to create the other trace
Then run a LWJDBC adaptor and insert "test" into that same bill_test.
I'm not sure if it will overwrite ifxjdbctrace or append to it, so you
may need to save it off under a different name between the map and
LWJDBC.

Once we have that information, we can see what the differences are, and
determine if we can change something within the map or present the
information to development, so they can review the code within the
mapper, to see why it's created differently

Thanks
Debby & Pat

----- History -----
IBM ECuRep Mail Gateway

Date:
Mon, 1 Apr 2013 14:21:03 -0400
Attachments:
To:
sterling_support@ecurep.ibm.com

Cc:
Geoff_Bernard@spartanstores.com, Peter_Logan@spartanstores.com
From:
Bill_Neumeyer@spartanstores.com
Reply-To:
Bill_Neumeyer@spartanstores.com
Subject:
RE: PMR 40858,422,000

Debby,

I am unable to convert the trace file using the SQLIPRINT utility.
It appears to be an issue directly related to this TEXT field.

I have several other trace files that are for other DBs and Tables
using the same JDBC Informix pool and driver. All of them convert using
the SQLIPRINT utility. The only difference I can find is that the
tracefile which fails to convert is the only that contains trace info for
inserts/queries against a TEXT column.

If you have any ideas on how to this trace file to convert to
ASCII values, please let me know and I will run it.

Thanks,

Bill Neumeyer

From: sterling_support@ecurep.ibm.com
To: Bill_Neumeyer@spartanstores.com
Cc: Peter_Logan@spartanstores.com, Geoff_Bernard@spartanstores.com
Date: 04/01/2013 12:50 PM
Subject: RE: PMR 40858,422,000

Bill

Thanks for getting up the first run through the simple map you created.
We will await the arrival of the trace run from the LWJDBC, if oyu have to
have the sqlprint utility and can run the file through to convert to text
we would appreciate it.

thanks
debby

----- History -----
IBM ECuRep Mail Gateway

Date:
Fri, 29 Mar 2013 13:48:52 -0400
Attachments:
To:
sterling_support@ecurep.ibm.com, Geoff_Bernard@spartanstores.com
ifxjdbctrace.txt1364572129522.1
Cc:
Peter_Logan@spartanstores.com
From:
Bill_Neumeyer@spartanstores.com
Reply-To:
Bill_Neumeyer@spartanstores.com
Subject:
PMR 40858,422,000

Debbie, Pat,

The attached file is the log results from the Informix driver
debug function during the single field map run.

Thanks,

Bill Neumeyer

From: Geoff Bernard/Corporate/Spartan
To: Bill Neumeyer/Consultant/Spartan@SpartanStore, Peter
Logan/Corporate/Spartan@SpartanStore
Date: 03/27/2013 02:20 PM
Subject: Fw: PMR 40858,422,000

Bill/Peter,
Have a read and then lets talk.

----- Forwarded by Geoff Bernard/Corporate/Spartan on 03/27/2013 02:18 PM
-----

From: sterling_support@ecurep.ibm.com
To: Geoff_Bernard@spartanstores.com,
Date: 03/27/2013 11:49 AM
Subject: Re: PMR 40858,422,000

Geoff,
Debby is out today, but I was one of the ones working with her previously.
Thanks for that information, it's exactly what we needed.

Everything in the pool looks fine, from what we can see.
At this point, our plan of action is to see what the differences are
between the LWJDBC and the Map's insert statements.
Since there is not a way within SI to see the insert, I've discussed with
a level 3 rep in Informix support on how to turn on the trace for
Informix.

Here is some documentation on it (see section 5.71):
http://www.redbooks.ibm.com/redbooks/pdfs/sg247884.pdf

Basically it looks like you'll just need to append to the url in the pool
(in the jdbc_customer.properties file and the
jdbc_customer.properties.in).
Depending on the operating system that SI is installed on, you'll need to
change the path to the trace file.

Using the ODS_DatabaseWork pool for an example,
If you're on Unix add ;SQLIDEBUG=/tmp/ifxjdbctrace so it looks like:
ODS_DatabaseWork.url=jdbc:informix-sqli://spartan24:9001/dbwrk:INFORMIXSERVER=ods_dev_tcp;SQLIDEBUG=/tmp/ifxjdbctrace

If you're on Windows add ;SQLIDEBUG=c:\\tmp\\ifxjdbctrace so it looks
like:
ODS_DatabaseWork.url=jdbc:informix-sqli://spartan24:9001/dbwrk:INFORMIXSERVER=ods_dev_tcp;
SQLIDEBUG=c:\\tmp\\ifxjdbctrace

You'll need to add a directory named tmp (or change the path to any
directory you want).
After you modify the property file, you'll need to start and restart the
services.

To keep this as simple as possible, create a map that connects to the
bill_test that Peter created (judging from the email you forwarded).
Just make sure that you modify the correct pool in the properties file.
Then just have a simple link from a string field on the input to the
bill_test field on the output, your input file can just pass the word
"test" or something simple.
When you run that map, it should create the ifxjdbctrace file, and contain
the insert that informix is receiving.
Then run a LWJDBC adaptor and insert "test" into that same bill_test.
I'm not sure if it will overwrite ifxjdbctrace or append to it, so you may
need to save it off under a different name between the map and LWJDBC.
Either way, we'll need to be able to determine which insert is from which
test.

We may need to tweak something else in the file, if that doesn't work, or
if nothing else, the L3 informix rep says that we can get the information
from the engine.
Once we have that information, we can see what the differences are, and
determine if we can change something within the map or present the
information to development, so they can review the code within the mapper,
to see why it's created differently.

I do apologize for you having to run these 2 tests for us, but as Debby
mentioned before, we do not have an informix instance that our systems can
access.
We are still trying to find a way to either get one locally, or fight thru
the red tape and system limitations to be able to connect.
But I feel that the quicker resolution is going to be for you to run these
tests and let us review your results.

Let me know if you have any questions, or just reply with the trace
results and we can go from there.

Thanks,
Pat

----- History -----
IBM ECuRep Mail Gateway

Date:
Wed, 27 Mar 2013 08:43:33 -0400
Attachments:
To:
sterling_support@ecurep.ibm.com
ifxjdbc.jar
Cc:

From:
Geoff_Bernard@spartanstores.com
Reply-To:
Geoff_Bernard@spartanstores.com
Subject:
Re: PMR 40858,422,000

Debby,
Here is the driver information.

----- Forwarded by Geoff Bernard/Corporate/Spartan on 03/27/2013 08:42 AM
-----

From: Ashok Aluri/Consultant/Spartan
To: Geoff Bernard/Corporate/Spartan@SpartanStore,
Cc: Cyrus Manesh <cmanesh@comresource.com>, Bill
Neumeyer/Consultant/Spartan@SpartanStore
Date: 03/25/2013 09:28 AM
Subject: IBM Informix JDBC driver and version

Ashok Aluri
ashok_aluri@spartanstores.com
Phone: 616-878-2468

IDS Forum is maintained by Administrator with WebBBS 5.12.