Truncate table before loading....Occassionally

[kris007]

Craig..I am not sure how occasionally it might be, that's the requirement.

Or even create a separate job in datastage to truncate table and run it manually whenever you want to truncate table....

Creating another job might be an option but, again I need to create whole lot of jobs for each job in my sequence(for each table I load data into) which as Naveen mentioned, and I feel is like wasting resources, because I would be running those jobs only once in a while. And, I need to insert data into my table once the table is truncated. So, I am kinda getting lost here. So need to regroup my thoughts here a bit. Will keep you all posted.

Thanks
Kris.

[ameyvaidya]

kris,
Just An Idea,

I am betting on the theory that if no rows go to a stage, it does not function. Cant test this though

Instead of one target OCI stage, Tyy to have 2 Target OCI stages.
Make these stages identical in all respects One with the truncte before-SQL and one without.

Add a Job parameter named Refresh.
In the transformer before the 2 stages, for these 2 output links, add the constraint "Refresh" and "not(Refresh)".

So if Refresh is given the value @TRUE, all rows should go to the OCI stage with the truncate before-sql.

if Refresh is given the value @FALSE, all rows should go to the OCI stage without the truncate before-sql.

Finally.. Untested Solution warning.

IHTH

[rleishman]

I've tried that before. Before and After SQL run whether there are rows or not.

I've done much the same as the previous suggestion, except instead of parameterizing the entire DELETE/TRUNCATE, I have a PL/SQL procedure that conditionally truncates the table.

Code: Select all

PROCEDURE truncate_table (
        pTab IN USER_TABLES.TABLE_NAME%TYPE
,       pWhere IN VARCHAR2 := NULL
) AS
        lDummy NUMBER;
BEGIN
        -- Evaluate the WHERE clause by attaching it to the SQL
        --      SELECT 1 FROM dual .....
        -- If it returns a row (i.e. WHERE condition is true), then
        -- go on to truncate the requested table.
        EXECUTE IMMEDIATE
                'SELECT 1 ' ||
                'FROM   dual ' ||
                pWhere
                INTO lDummy;

        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || pTab;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
                NULL;
END truncate_table;

Then I put the following in my Before-SQL:

Code: Select all

call truncate_table('TABLE_NAME','WHERE #PARAM# = 1')

or more sneakily:

Code: Select all

call truncate_table('TABLE_NAME','WHERE EXISTS(SELECT 1 FROM load_table)')

[rasi]

Amey

Instead of one target OCI stage, Tyy to have 2 Target OCI stages.
Make these stages identical in all respects One with the truncte before-SQL and one without.

Regardless of whether you have rows or not both the links Before SQL will run. This solution will truncate the records for every run

[kris007]

Amey,

I have tried doing that. Right now my job has a transformer with two links going into the OCI stage. One link for inserting new rows and second link for updating existing rows only based upon the lookup of key columns from the target table.[img]

Target--key columns-->> HashedFile
|
|
Sequential File--i/p rows-->TX----insert---->OCI
|
------update--->OCI[/img]

This is how my loading job looks. IF linkNOTFOUND=@TRUE then insert else update. I have tried giving a third link to OCI where my Update action in OCI was Truncate table and then insert rows and have given update action as parameter. But for some reason my table was being truncated for every alternate job run even if I didnt pass the refresh parameter. So I gave up on that one and was looking for a different approach like before sql or passing the truncate statement as a parameter in my staging job.( A job which I developed to extract data from source. The job shown above is my loading job). But, as you have mentioned..a little differently I will try giving a before query for the third link, pass update parameter and see how it works.

Ross..Thanks for the procedure. I will try using that approach and let you know how it worked.

Thanks all.
Kris.

[kris007]

Regardless of whether you have rows or not both the links Before SQL will run. This solution will truncate the records for every run

Thanks Siva....That is what even I thought ..but just needed to confirm it before I could say something. You saved my time.

Kris.

[chulett]

Ok. From what I understand of your needs here's what I would do in your shoes, Kris, other than walk funny that is.

Create a job that just truncates your table and does nothing more. This is perfectly valid and something I've done many times before. All you need is a transformer and a database stage. Create a dummy stage variable in the transformer so the compiler is happy, one you don't even need to use. Put at least one column from the table in the link between the two stages. Set your constraint to @FALSE and your Update Action to 'Truncate then insert'. As noted earlier, no rows will go down the link - it will simply truncate the table when run.

Create a parameter in your Sequence job to control this, something like TruncateTable let's say. Parameter type is List with valid values of 'Y' and 'N' - make sure you default it to 'N' so that someone has to explicitly switch it to 'Y' to make this happen. Note these two values could be anything you desire to check for, even "No, don't even think about truncating!" and "Oh My Gawd, are you really *really* sure you want to truncate them tables?".

Start your jobstream off with a Nested Condition stage, the one with the question mark that allows you to make 'decisions' in your stream. Use it to either run or branch around the truncate job by checking the job parameter. Use two triggers:

Code: Select all

TruncateTable='Y'    Links to the truncate job immediately after the NC stage
TruncateTable='N'    Links to a Sequencer set to 'Any' immediately after the truncate job

Note the non-use of enclosing # for the parameter name here. If you've used the...err, 'long' versions noted above you could use the substring operator to check TruncateTable[1,1]="N" or "O". Of course, there is a link between the truncate Job Activity stage and the Sequencer stage.

After the Sequencer stage would come the jobs that you were normally running. Setup properly, this allows you to typically skip the truncate job (or jobs) at the head of the stream and dive right in just after that point. Occassionally, you switch the parameter to a 'Y' and it runs the truncate job(s) first in the stream.

Easy to setup, understand and maintain IMHO... much more so than magic sql buried away in before SQL tabs that most people will never know is there. Unless you are a firm believer in Annotating your jobs, that is - which you definitely should be.

My two cents.

[DSguru2B]

When i initially gave my idea of passing a parameter in the before tab of the sql, i actually tested that with the DRS stage. I couldnt test it with the OCI because we dont have Oracle here at our client place.
In the DRS stage, even if you pass nothing, it would work, without any warnings. I have never used the OCI stage so i cannot comment on that.
chulett's idea is much more firm, more controlable and maintainable.
Glad to know we are under supervision of our GURUS.
Cheers.

[kris007]

I've tried that before. Before and After SQL run whether there are rows or not.

I've done much the same as the previous suggestion, except instead of parameterizing the entire DELETE/TRUNCATE, I have a PL/SQL procedure that conditionally truncates the table.

Code: Select all

PROCEDURE truncate_table (
        pTab IN USER_TABLES.TABLE_NAME%TYPE
,       pWhere IN VARCHAR2 := NULL
) AS
        lDummy NUMBER;
BEGIN
        -- Evaluate the WHERE clause by attaching it to the SQL
        --      SELECT 1 FROM dual .....
        -- If it returns a row (i.e. WHERE condition is true), then
        -- go on to truncate the requested table.
        EXECUTE IMMEDIATE
                'SELECT 1 ' ||
                'FROM   dual ' ||
                pWhere
                INTO lDummy;

        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || pTab;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
                NULL;
END truncate_table;

Then I put the following in my Before-SQL:

Code: Select all

call truncate_table('TABLE_NAME','WHERE #PARAM# = 1')

or more sneakily:

Code: Select all

call truncate_table('TABLE_NAME','WHERE EXISTS(SELECT 1 FROM load_table)')

Hi Ross Leishman,
I pretty much did the same thing but in my case i even needed 2 OUT parameters. It gives me error and there by aborting the job.
Here is my code :

Code: Select all

REATE OR REPLACE PROCEDURE UPDATEACTION( ACTION IN CHAR,HIREDAT  OUT VARCHAR2, DUMMY  OUT CHAR)
IS
BEGIN
IF ACTION ='R' 
THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE SCOTT.EMP2';
HIREDAT:=NULL;
ELSE
SELECT MAX(HIREDATE) INTO HIREDAT FROM SCOTT.EMP2;
END IF;
DUMMY:='Z';
END;

When I call it using :

Code: Select all

call truncate_table('TABLE_NAME','','')

I get variable not suitable for assignment (or something like that).
when I give output column names

Code: Select all

call truncate_table('TABLE_NAME','HIREDATE','DUMMY')

It gives error too, I tried calling this way:

Code: Select all

call truncate_table('TABLE_NAME',:1,:2)

where :1 , :2 are defined in columns tab of the stage.
I think I am missing knowledge of how to capture result set.
I even tried defining OUT parameters as IN OUT parameters in PROCEDURE definition as I read somewhere ODBC stage only allows IN and IN OUT parameters. I think I am doing something wrong with passing OUT parameters. Can any one help me on that.

[DSguru2B]

If you are rarely truncating tables, another thing would be just to load the table with /dev/null . This how i have done quite many times in DB2.

[kris007]

Create a job that just truncates your table and does nothing more. This is perfectly valid and something I've done many times before. All you need is a transformer and a database stage. Create a dummy stage variable in the transformer so the compiler is happy, one you don't even need to use. Put at least one column from the table in the link between the two stages. Set your constraint to @FALSE and your Update Action to 'Truncate then insert'. As noted earlier, no rows will go down the link - it will simply truncate the table when run.

Create a parameter in your Sequence job to control this, something like TruncateTable let's say. Parameter type is List with valid values of 'Y' and 'N' - make sure you default it to 'N' so that someone has to explicitly switch it to 'Y' to make this happen. Note these two values could be anything you desire to check for, even "No, don't even think about truncating!" and "Oh My Gawd, are you really *really* sure you want to truncate them tables?".

Start your jobstream off with a Nested Condition stage, the one with the question mark that allows you to make 'decisions' in your stream. Use it to either run or branch around the truncate job by checking the job parameter.

That works great Craig. Thanks a lot..and thanks to everyone.
Cheers :D
Kris

[rleishman]

I pretty much did the same thing but in my case i even needed 2 OUT parameters. It gives me error and there by aborting the job.
...

I would be surprised, nay flabbergasted, if you could pass OUT params from a stored proc back to DS using the Before/After-SQL.

If you MUST use a stored proc to supply data via OUT params, you are going to need the Stored Proc Stage. 10 minutes playing with this should convince you that there are much nicer ways to supply data - eg. A normal SELECT in an OCI stage.

[kris007]

I would be surprised, nay flabbergasted, if you could pass OUT params from a stored proc back to DS using the Before/After-SQL.

even tried defining OUT parameters as IN OUT parameters in PROCEDURE definition as I read somewhere ODBC stage only allows IN and IN OUT parameters. I think I am doing something wrong with passing OUT parameters. Can any one help me on that.

.

But you are right I find ODBC or STP stage not that useful.. (or may be becoz I couldnt find my way around .).