DB2 in awk

Shell Programming and Scripting
1

Hi,

I want to retrieve the base table name for a table and replace the table with the view name in the file.

I was trying to retrieve the base with the below db2 command but i am getting "1" as return value.

Could you anyone please assist ?

 Table_name=system("db2 -x \"SELECT TRIM(TD.BSCHEMA) || \'.\' || TRIM(TD.BNAME) AS TABLE_NAME FROM SYSCAT.TABDEP TD WHERE TD.BTYPE=\'T\' AND TD.TABSCHEMA=\'XXXXXXX\' AND TD.TABNAME=\'\"$View_Name\"\' or
der  by BSCHEMA desc fetch first 1 row only\"");
    print Table_name;

Regards,
Nantha.Y

---------- Post updated at 05:21 AM ---------- Previous update was at 04:51 AM ----------

Hi ,

Input line : Left Outer Join XXXXXX.YYYYYYY_YYYYY_YYY

where XXXXX is VIEW_SCHEMA and YYYYY_YYYY_YY is VIEW_NAME

Objective : Replace the VIEW NAME with base table name

Output : Left Outer Join ZZZZZZ.AAAAAAA
where ZZZZZ : Base table schema
AAAAAA is Base Table name.

Regards,
Nantha.Y

2

RudiC had made a suggestion in your other post; I'll expand on it here:

(1) Print or echo the query before passing it to DB2.
(2) Or just print it and do not pass it to DB2.
(3) When you have the query in front of you, have a close look at it. Maybe there's something wrong with it.
(4) If you think the query is correct, copy and paste it in your ad-hoc query tool (IBM Data Studio or TOAD for DB2 or whatever you are using.) Then execute it and check the result.
If you do not have a graphical tool, then run your query in the DB2 command-line processor.

3

You mean Table_name contains a "1"? Not too surprising ( man awk ):

You might try "db2 -x \"SELECT TRIM... " | getline Table_name , given your db2 command yields some correct result.

4

Hi RudiC,

The query works perfectly.

My intention to store the result of the query in a variable which i cant do it through system function.

Is it the problem with AWK ?

Regards,
Nantha.Y

5

No problem with awk , it's the specified behaviour. Did you try the proposed alternative?