Help with "timestamp" for OCI-9

[ady]

When we are inserting the rows there are 4 primary keys in the rows. The combination cannot be the same because their derivations are "DSjobstarttime stamp" , "Current date and tiimestamp", "another Timestamp from the source" and finally a "ID" . "ID" and "Timestamp from source" can be same at times but the same combination of the data in all the primary key columns does not occur.

Infact we are able to insert rows when we give the Source Timestamp to another non-key column. I do not know the exact reason behind this.

We cannot alter the keys of the table on the database because the same table is being used by other teams and they dont want to make the change. So for the moment we are writing the Source Timestamp to a non key column.

[chulett]

I don't really understand why this is such a problem or how we got to three pages. These are standard 'issues' you'd have to deal with in any database or even a 'key index' file system on any given day. Let's see if we can cut through the confusion and get to the bottom of this.

There are not 'four Primary keys' as you can only have one Primary Key - so you must mean the PK is a composite key comprised of four columns.

Can you post the full DDL for the table, including the 'alters' that would show the PK or any other constraint? Your DBA can get that for you if you don't have something like Toad and can do it yourself. It's so we can understand your target.

And there shouldn't be any reason to 'alter the keys', we just need to help you understand how to properly populate a record for that table. Or how to report to your Architect or Modeler that the design on the table is fundamentally flawed and cannot be used to store your data. We shall see.

[DSguru2B]

DSJobStartTimestamp will be the same for all the records in a particular job.
Current Date and Current Time from DATE() and TIME() will be the same for the first several records.
The remaining two can be same as you said. There you go. You have identical combinations of your keys, or composite key.
I say change the column for Current Timestamp from a DATE field to Timestamp. This way you can insert fractional seconds as well.
OR, you can askt he DBA to default that column to CURRENT_TIMESTAMP. This way you dont have to pass any value and it will generate a timestamp everytime there's an insert.

[ady]

@Chulett

Yes we are using the combination of 4 columns. I cannot post the DDL for the table as I dont have it and there is no way I am getting it as the DBA is out of my reach.


@DSguru28


We are writing only 1 row to the table when we run the job, so we cant have the same DSjobstarttimestamp or current timestamp. The row we are writing is kind of a log, so that when our job runs it leaves a log in that table as a new ROW.

[I_Server_Whale]

Can you atleast capture this row into a sequential file and post the results.

Please make sure that you run the job so that we have 5-10 records. This will ensure us to look at the data and getting a better understanding.

Also, post the metadata of your target DB table. Not the metadata from your target stage.

Thanks,
Whale.

[chulett]

Okay... so... no TOAD, no DBA access or access to anyone (like a Modeler) that could fulfill a simple DDL request? Good luck.

How about running a couple of queries and then posting the results?

Code: Select all

SELECT table_name, column_name, data_type, data_type_mod, data_type_owner,
       decode(data_type, 'CHAR', char_length,
                         'VARCHAR', char_length,
                         'VARCHAR2', char_length,
                         'NCHAR', char_length,
                         'NVARCHAR', char_length,
                         'NVARCHAR2', char_length,
                         data_length) data_length,
       data_precision, data_scale, nullable, char_used
, user owner
FROM SYS.ALL_TAB_COLUMNS c
WHERE OWNER = '????'
 and TABLE_NAME = '????'
ORDER BY table_name, column_id

Code: Select all

SELECT index_owner, index_name, column_name, column_length, table_owner, table_name, column_position 
, descend
FROM SYS.ALL_IND_COLUMNS
WHERE TABLE_OWNER = '????'
 and TABLE_NAME = '????'
ORDER BY index_owner, index_name, column_position

Copy and paste them into your query tool of choice and change the ???? for OWNER and TABLE_NAME to match our new bestest friend here.

Post the results.

[ady]

<a href="http://server6.theimagehosting.com/imag ... g=ORA9.jpg" target="_blank"><img src="http://images6.theimagehosting.com/ORA9.th.jpg" border="0" title="The Image Hosting" /></a>


Thats the table metadata. and here's the output



<a href="http://server6.theimagehosting.com/imag ... UT.b8f.jpg" target="_blank"><img src="http://images6.theimagehosting.com/OUTPUT.b8f.th.jpg" border="0" title="The Image Hosting" /></a>

[I_Server_Whale]

Hi,

Can you execute the following query to know the constraints defined on your target table.

Code: Select all

select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME, STATUS, INDEX_NAME from
 USER_CONSTRAINTS where TABLE_NAME = 'Your_Target_Table_Name';

and post the results.

Then we can relate the output data to the constraints defined on your table.

Whale.

[ady]

I know you guys would like to shoot me after I say this but .... I dont have any tool to run a query on , I am trying to get one installed.

Anyway the Team has decided to change the Source timestamp to another field which makes the job run well ( atleast for now ). No changes can be made to the table by us coz they are many other teams loading to the same table and they just voted against us to make changes to the table !


You guys have been great ... thanx for all the help on this issue !

I'll mark the topic resolved for now , will reopen if we want to pursue this further. Thanks again

[I_Server_Whale]

Thanks !

You guys have been great ... thanx for all the help on this issue !

I'll mark the topic resolved for now , will reopen if we want to pursue this further. Thanks again

and that's a good thought.

[chulett]

I dont have any tool to run a query on

You should have access to command line sqlplus at a minimum, unless you don't even have a logon to the server where the Oracle database is installed. If that's the case... jeez - it sucks to be you!

Kind of hard to do your job without the tools you need to do it properly...

ps. There *are* freeware tools available that use ODBC or JDBC to connect, so are fairly easy to setup - a Google should turn them up. I vaguely remember one called... SQuirreL? Or TORA? Perhaps others have more specific recommendations, I've been using Toad for years so really haven't considered anything else.

[ray.wurlod]

If you have the Oracle client server installed on the DataStage server machine you have sqlplus on that machine. If you don't have the Oracle client software installed and properly configured, then you can't use DataStage to connect to Oracle. As far as Oracle is concerned, DataStage is just another client application.