User defined SQL in Datastage 4.0
User defined SQL in Datastage 4.0
Ive worked through several issues with tech. support, some successfully, some not so, with use of User defined SQL(UDS). Im running Datastage 4.0 on Sun/Solaris. I use UDS in situations with Oracle 8 OCI where I need to populate synthetic keys with sequence values. Ive used Datastages key processing transforms, but environment standards require that I actually use the database sequences. I usually create the UDS by copying the generated SQL and customizing it. Many times the generated SQL will work correctly, substituting a literal for sequence numbering, but the UDS will fail. Dates seem to be a particular problem, but I get a number of varied Oracle errors with UDS. Is my frustration with UDS unique or is it a known problem? Are ther any other ways to use Oracle sequences without needing UDS? Generated SQL and UDS seem to go through different pre-processing steps, is that the case? Thanks Bob
Bob,
Ill try the abbreviated answer. If you need more info, let me know.
Using Oracle sequences. The approach you are using is fine. Alternatively, you could use a reference link to get the sequence value. It to would require a user defined query, although it does not involve dates. The user defined query would be something simple like
SELECT sequence.NEXTVAL FROM DUAL
To persist with your current approach just requires a little understanding of how DataStage interacts with Oracle.
From an Oracle perspective, treat the DataStage "Timestamp" type as a string with a very specific format. In Oracle terminology, it is YYYY-MM-DD HH24:MI:SS. If you remember that Oracles default date format is DD-MON-YY, then you will understand why Oracle is not happy with the string that DataStage is giving it, or why DataStage is not happy with the input date from user defined input queries.
What DataStage doesnt show you is that when it generates queries with the Oracle OCI interface, it does a TO_CHAR when reading dates and TO_DATE when writing dates.
So, in your user defined INSERT statement, if, for example, field 5 was a date, then the expression in the value list for the insert would be:
TO_DATE(:5, YYYY-MM-DD HH24:MI:SS)
If you remember this whenever you have dates in user defined queries, be they inputs, lookups or outputs, you should be fine.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Bob_2_Whiteside@sbphrd.com [mailto:Bob_2_Whiteside@sbphrd.com]
Sent: Monday, 16 July 2001 8:28 PM
To: datastage-users@oliver.com
Subject: User defined SQL in Datastage 4.0
Ive worked through several issues with tech. support, some successfully, some not so, with use of User defined SQL(UDS). Im running Datastage 4.0 on Sun/Solaris. I use UDS in situations with Oracle 8 OCI where I need to populate synthetic keys with sequence values. Ive used Datastages key processing transforms, but environment standards require that I actually use the database sequences. I usually create the UDS by copying the generated SQL and customizing it. Many times the generated SQL will work correctly, substituting a literal for sequence numbering, but the UDS will fail. Dates seem to be a particular problem, but I get a number of varied Oracle errors with UDS. Is my frustration with UDS unique or is it a known problem? Are ther any other ways to use Oracle sequences without needing UDS? Generated SQL and UDS seem to go through different pre-processing steps, is that the case? Thanks Bob
Ill try the abbreviated answer. If you need more info, let me know.
Using Oracle sequences. The approach you are using is fine. Alternatively, you could use a reference link to get the sequence value. It to would require a user defined query, although it does not involve dates. The user defined query would be something simple like
SELECT sequence.NEXTVAL FROM DUAL
To persist with your current approach just requires a little understanding of how DataStage interacts with Oracle.
From an Oracle perspective, treat the DataStage "Timestamp" type as a string with a very specific format. In Oracle terminology, it is YYYY-MM-DD HH24:MI:SS. If you remember that Oracles default date format is DD-MON-YY, then you will understand why Oracle is not happy with the string that DataStage is giving it, or why DataStage is not happy with the input date from user defined input queries.
What DataStage doesnt show you is that when it generates queries with the Oracle OCI interface, it does a TO_CHAR when reading dates and TO_DATE when writing dates.
So, in your user defined INSERT statement, if, for example, field 5 was a date, then the expression in the value list for the insert would be:
TO_DATE(:5, YYYY-MM-DD HH24:MI:SS)
If you remember this whenever you have dates in user defined queries, be they inputs, lookups or outputs, you should be fine.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Bob_2_Whiteside@sbphrd.com [mailto:Bob_2_Whiteside@sbphrd.com]
Sent: Monday, 16 July 2001 8:28 PM
To: datastage-users@oliver.com
Subject: User defined SQL in Datastage 4.0
Ive worked through several issues with tech. support, some successfully, some not so, with use of User defined SQL(UDS). Im running Datastage 4.0 on Sun/Solaris. I use UDS in situations with Oracle 8 OCI where I need to populate synthetic keys with sequence values. Ive used Datastages key processing transforms, but environment standards require that I actually use the database sequences. I usually create the UDS by copying the generated SQL and customizing it. Many times the generated SQL will work correctly, substituting a literal for sequence numbering, but the UDS will fail. Dates seem to be a particular problem, but I get a number of varied Oracle errors with UDS. Is my frustration with UDS unique or is it a known problem? Are ther any other ways to use Oracle sequences without needing UDS? Generated SQL and UDS seem to go through different pre-processing steps, is that the case? Thanks Bob