DSXchange

--If you are using double quote(" "), then inside the data musn't contain any DOUBLE quote as it will crash the reading of csv file. This is what is happening right now and what I'm trying to get around. If you keep getting data in this way it might be a good idea to write a robust routin...

Thanks for the replies. Yes, I believe I will have to look in the file for quotes that are not preceded or followed by a comma or a line terminator and isnt the first character on the line. I do not know if DataStage BASIC allows for such regular expressions. I would like to get it fixed at the exte...

I am using folowing code to convert into db2 format: OCONV(ICONV(linkname.DATE1,"DMDY"),"D4/MDY[2,2,4]") I tried inserting the Date value in all the possible DB2 formats, by changing ICONV/OCONV formats,but no use. You could use the transform DateGenericToODBC to convert your da...

Thanks for the reply Craig. I've tried this but now there is another problem, the data also has commas in it, so disabling the quote character or removing quotes altogether causes the field to be read as 2 fields. Is it possible do something like removing quotes inside quoted strings or escape it fo...

Here's whats in the DB2 Documentation .. SQL30082N Attempt to establish connection failed with security reason "<reason-code>" ("<reason-string>"). Explanation: The attempt to connect to the remote database server was rejected due to invalid or incorrect security information. 17 ...

I have a job that reads in a comma separated file with " (double quotes) as the quote character and this was working fine for the last few months until today when we received a file which had a double quote in the data. The particular record looks like the below. ...,"White","LX ...

DB2 stores timestamps with microseconds ie 2000-07-31-10.30.15.610208 You can try inserting by formatting it into YYYY-MM-DD-hh.mm.ss.zzzzzz If its user defined SQL then you can also use the DB2 timestamp functions. To convert a character string to a date or time value, you can use: TIMESTAMP ('2004...

The locks that I refer to are actually the locks in the database on the tables. Datastage shows the job as aborted but bringing up the performance monitor on DB2 apparently shows some active threads still holding locks on the table. I tried setting the transaction isolation to cursor stability/Read ...

I have to do the reverse of what another op posted which is to pad a numbers with zeros. Also, The first char needs to be '-' fro -ve numbers or blank for +ve numbers. 123.00 -> ' 00000123.00' -25.30 -> '-00000025.32' 12 -> ' 00000012.00' -0.45 -> '-00000000.45' And I've written the following in the...

I am trying to recreate this problem and try the 'Cleanup Resources" option in Director to see if it helps. I am not sure if it pertains to locks inthe database. However, even if it does, I suspect this is a temporary fix since if one of the other scheduled daily jobs fails in production, then ...

One of the guys that I work with suggested that setting the Isolation to Cursor Stability might help. Does this help?

I am trying to find a good book which has more about these settings so anything you guys can recommedn will also be helfpful.

thanks,
paul

We have a few jobs that try to insert about 100,00 rows into certain tables in DB2. In this particular instance, what happened was that teh tables size was not defined properly and therefore tha table/tablespace got full and therefore datastage gave an error saying the resource was unavailable. All ...

Thank you for all your replies. One more question is how does the sign come into play here? Will I need an extra character? I am tempted to just re-define the layout for the flat file to day that the field a_amount is of type char(12) with one extra char for the sign and one for the decimal point bu...

In my project we are using flat files to interface with external systems. In the interface layout specification, there are certain columns with an implied decimal&sign which need to be mapped to a decimal(10,2) field in DB2 using an ODBC stage. I am not sure how to handle implied decimal fields ...

Great! Works like a charm.. thanks guys.