BTEQ script in Shell Script

Shell Programming and Scripting
1

Hi Team,

I have a requirement where I need to check for table. If exists, drop it else do nothing. For that I written below code.

T_NAME="V_"`echo ${PARAM_BASE} | cut -d'_' -f4-` 

tab_name="UV_"${T_NAME} 

query_et() 

{ 

bteq << EOBTQ | grep '^>' | sed -e "s/^>//" 

.LOGON DEVEDW1/<username>,<password>; 

SELECT count(*) from dbc.tables where databasename = '${SCHE_NAME}' and tablename = '${tab_name}' having count(*) > 0; 

.IF ERRORCODE <> 0 THEN .QUIT 10;  

.IF ACTIVITYCOUNT = 1 THEN .GOTO DROP_TAB;  

.LABEL DROP_TAB;  

DROP TABLE ${SCHE_NAME}.${tab_name}; 

.LOGOFF 

.QUIT; 

EOBTQ 

}

The problem is, even if table does not exists, it goes to DROP statement... I do not understand why it goes there even after I put ACTIV ITY COUNT = 1. Can someonne please help me to figure out the problem?

2

Where are SCHE_NAME and PARAM_BASE being set?

3

At the top of the script. Issue is

SELECT count(*) from dbc.tables where databasename = '${SCHE_NAME}' and tablename = '${tab_name}' having count(*) > 0;

returns null. I have given

.IF ACTIVITYCOUNT = 1 THEN .GOTO DROP_TAB;

BUT still it goes to

DROP TABLE ${SCHE_NAME}.${tab_name};
4

In:

SELECT count(*) from dbc.tables where databasename = '${SCHE_NAME}' and tablename = '${tab_name}'

shell variables are not expanded inside single quotes. Try:

SELECT count(*) from dbc.tables where databasename = "${SCHE_NAME}" and tablename = "${tab_name}"

but, even then, as Scrutinizer said, SCHE_NAME has to be defined before it is expanded in your script.

5

That is not the issue. Code is working fine if table exists. BUT if table does not exists, it still goes to drop it, which I do not want it to do.

6

Not sure if my logic fails me but to me that snippet as given reads "if activity then goto drop_table else goto drop_table". Please prove me wrong.

7

Rudic, what chage you are suggesting in order to make it work?

8

I have no way to test what you're doing, but consider changing:

.IF ACTIVITYCOUNT = 1 THEN .GOTO DROP_TAB;

to:

.IF ACTIVITYCOUNT = 0 THEN .GOTO LOG_OUT;

and adding:

.LABEL LOG_OUT;

just before the line:

.LOGOFF

Given the format of all of your other bteq statements, should there be a semicolon at the end of the .LOGOFF statement?

9

I can't tell, as I have no command of bteq nor any idea of what values ACTIVITYCOUNT can assume nor what these represent, and what action should follow on either of those.
Did you understand what I alluded to and how you would / could change the program flow?
BTW, other languages use = for an assignment, and == for a comparison operation. You might want to check what bteq requires.

10

As a side note: in this case the single quotes are not relevant for the expansion, because they are situated inside an unquoted here-doc, so the variables are expanded and the results are placed inside the single quotes..

1 Like
11

Did you try something like :

[...]
.IF ACTIVITYCOUNT = 1 THEN .GOTO DROP_TAB;   
.GOTO LOG_ME_OFF
.LABEL DROP_TAB;
DROP TABLE ${SCHE_NAME}.${tab_name};  
.LABEL LOG_ME_OFF
.LOGOFF
.QUIT;   
 EOBTQ 
[...]

or

[...]
.IF ACTIVITYCOUNT = 1 THEN DROP TABLE ${SCHE_NAME}.${tab_name};
.LOGOFF
.QUIT;
[...]
12

Maybe I'm missing the point, but what's wrong with ignoring the test and just trying to drop the table anyway. You can add the IF EXISTS to avoid getting error messages if it doesn't. it could be as simple as this:-

DROP TABLE IF EXISTS ${TABLE_NAME} ;

I hope that this helps, but I apologise if I've missed the point.
Robin