SQL not wowrking in Before/After Tab of Teradata API

maffan76 · Post by **maffan76** » Sat Sep 24, 2005 3:41 am

Hi,
I am inserting and then deleting the records from the base table which contain missing key columns into another table in the Teradata API before selecting the data from that table but apparently the SQL in BEFORE TAB does not seem to work. When i go back and check the base table it contains the records and no record is inserted in error logging table.

Any idea what i am doing wrong here?

Thanks In Advance.

rleishman · Post by **rleishman** » Sat Sep 24, 2005 5:10 am

I use Oracle, not Teradata, but to help I think even Teradata gurus will need to know more.

Can you establish whether the before-SQL is actually being run? If you have a look at the job log in Director, there should be an Info entry for each successful statement in the Before-SQL, and a Warning entry for each failed statement. If Teradata has a "Treat Errors as Warnings" checkbox on the before-SQL, try unchecking it so that any error in the before-SQL is raised as an Abort.

If every SQL in the Before-SQL has an Info entry, then there's something wrong with your SQL; cut it out of Director (so that you get any Job Parameter replacements) and run it in your Teradata SQL client to make sure it works.

If there are Warning/Abort lines, deal with it; you have an error in your SQL.

If the SQLs don't appear in Director at all (careful here...you may need to check a box in the Job Parameters page to make every event log...I'm not at work and cannot check) then there's something about the way you have expressed them in the Before-SQL that is upsetting DataStage. Make sure you have a double semi-colon (;;) between SQLs.

If none of this helps, paste the entire Before-SQL into this thread so that the guru's can see it.

maffan76 · Post by **maffan76** » Sat Sep 24, 2005 5:38 am

Ross,
Thanks for the reply the SQL is perfect as i have tested in Client Software and if my stage sends out put to other stage it does not work but on the other hand it receives data the SQL in before stage works.

and can yo please tell me from where i can invoke the job paramater page as if i press Ctrl+J in designer I get the job properties page but i dont see any option there for logging.

rleishman · Post by **rleishman** » Sat Sep 24, 2005 5:48 am

Dude, where the hell are you - it's Saturday all over the world and I refuse to believe you have less social life than me.

Ah it's coming back now. Not a Job Property (oops

, did I say Parameter before) but a Job Sequence setting used to log each activity in the sequence. Forget it; it doesn't control the messages logged from within the job. Sorry for wasting your time.

I'm prrrrrrretty sure that every SQL should be logged regardless. Are they being logged?

maffan76 · Post by **maffan76** » Sat Sep 24, 2005 5:55 am

Ross,
I am in Saudi Arabia

and its working day over here Week end is THU and FRI

so i am on work though here today is a National Holiday but i have to meet a deadline so i am working

I am checking the LOG and i have unchecked the SQL error but still no error in LOG. So i am now confused that whats hapening.

Before SQL TAB contains Update Statement, Delete Statement and Generated TAB contains generated SQL Statement. What could be wrong?

rleishman · Post by **rleishman** » Sat Sep 24, 2005 6:01 am

Affan,

Once again, I don't use Teradata, but even if the SQL is successful it should appear in the log. Look at EVERY Info message from the start of the job to where the Terdata Plugin starts reading rows.

Could you paste the SQL into your reply and enclose it in the CODE tags. I'm packing up soon, so make it snappy.

maffan76 · Post by **maffan76** » Sat Sep 24, 2005 6:04 am

Ross Here is the Before TAB Query

UPDATE DD_STG_COMPGSM.CDRs_Ericsson_MSC
SET CDR_File_Seq_No = '1'
WHERE EMRFR_ORIG_National_No = '555244391';

Here is the Generated Query

SELECT CDR_Record_Type,CDR_Creation_Date,CDR_Creation_Time,CDR_File_Seq_No,Record_Type,EMRFR_ORIG_National_No,EMRTO_Termnat_No,EMRBSC_Bill_Serv_Code,EMRBL_Bill_Serv_No,EMRDTE_Date_Of_Call,EMRCTH_Conn_Time_HH,EMRCTM_Conn_Time_MM,EMRTMM_Convers_Time_MM,EMRTSS_Convers_Time_SEC,EMRTYP_Typ_Of_Call,EMRAM1_Charge_Amt,EMJUR_Jurisd_Of_Call,EMRACT_Priced_RePriced,EMRCTS_Conn_Time_SEC FROM DD_STG_COMPGSM.CDRs_Ericsson_MSC;

maffan76 · Post by **maffan76** » Sat Sep 24, 2005 6:05 am

In the Log it only Says

"Update_BeforeSQL..Teradata_API_1: Executing BeforeSQL"

No Detailed Description.

maffan76 · Post by **maffan76** » Sat Sep 24, 2005 6:09 am

Any Idea Whats Trace mode and in Log its showing tracemode=0 and if i want to set it how can i set it.

rleishman · Post by **rleishman** » Sat Sep 24, 2005 6:19 am

Hmmm. I don't think I am going to be any help here.

Didn't you say you were transferring the rows to another table and then deleting them from the source table? That would take a couple of SQLs but you have shown me only one

OK - I'm making a big assumption that the Teradata plugin logs the same way as the Oracle one, but if the log message does not list the SQL, then I'd say that DS is passing an empty string to Teradata (hence the reason it's not doing what you hoped).

One of my staff had a similar problem when we first started experimenting with DS (before I got my hands dirty myself). He was trying to get a stored procedure to execute, and was finding that with various combinations the call would either fail (because we were not calling it properly) or it would not call at all because the database received an empty string.

We never really got to the bottom of the empty string problem, because once we called the proc correctly (with "Call sp_name") our problem was solved.

The things he was mucking about with when he got the empty call problem were things like:
- blank line at the top of the before-sql
- blank lines between SQLs in the before-sql
- single and double semi-colons between SQLs in the before-sql

Try this (it might just work

):
- Remove all blank lines in the before-sql
- If you have 2 or more statements in the before-sql, ensure that each is separated by a two consecutive semi-colons. I notice you used a single one in the SQL you pasted.

maffan76 · Post by **maffan76** » Sat Sep 24, 2005 6:23 am

Thats true i pasted only one as lately i was trying only one to see that is is a problem with multiple SQLs or problem is with All SQLs.

I'll try this solution and will let you know if it works.

Thanks again for all the help and support.