Help with ERRORCODE.

Database

Help with ERRORCODE.

HELLO, am trying to validate a showtable in a sql embed into a shellscript code, the issue is that i wish to find out how to get if the show table works or not, and then go on in one way or another,

export TABLA1= originaltable
export TABLA2= originaltable_old

################################################################
# EJECUCION DE SQL QUE BORRA LA TABLA2
################################################################

# Validamos que exista TABLA2
file_sql=${DATA_DIR}/${CADENA}.sql
cat /dev/null > ${file_sql}

echo "DATABASE ${BASEDATOS}" >> ${file_sql}
echo ";" >> ${file_sql}
echo "SHOW TABLE ${TABLA2};" >> ${file_sql}
echo ".IF ERRORCODE = 0 THEN .GOTO SIGUIENTE0;" >> ${file_sql}
echo ".QUIT 0;" >> ${file_sql}
echo ".IF ERRORCODE <> 0 THEN .GOTO SIGUIENTE;" >> ${file_sql}
echo ".LABEL SIGUIENTE0;" >> ${file_sql}
echo "DROP TABLE ${TABLA2};" >> ${file_sql}
echo ".QUIT 0;" >> ${file_sql}
echo ".LABEL SIGUIENTE;" >> ${file_sql}
echo "SHOW TABLE ${TABLA1};" >> ${file_sql}
echo ".IF ERRORCODE = 0 THEN .GOTO SIGUIENTE2;" >> ${file_sql}
echo ".IF ERRORCODE <> 0 THEN .GOTO SIGUIENTE3;" >> ${file_sql}
echo ".LABEL SIGUIENTE2;" >> ${file_sql}
echo "RENAME TABLE ${TABLA1} TO ${TABLA2};">> ${file_sql}

the purpose of this code is to validate if originaltable_old, exist.. then if it does drop it, if not go to SIGUIENTE0, and ask again for another show table...
please help to anyone.
4 REPLIES
Enthusiast

Re: Help with ERRORCODE.

You can better use some bteq tricks to do this sort of work

#/usr/bin/ksh

PRODDB=prod1db; export PRODDB
MYTBL=acctstbl; export MYTBL

bteq <<- END

.run file mylogon;

SELECT TABLENAME
FROM DBC.TABLES
WHERE DATABASENAME = ${PRODDB}
AND TABLENAME = ${MYTBL}
;

.IF ACTIVITYCOUNT = 0 THEN .GOTO NODROP;

DROP TABLE ${PRODDB}.${MYTBL};

.LABEL NODROP;

.LOGOFF;

.QUIT;

END
Enthusiast

Re: Help with ERRORCODE.

Well yes u should use BTEQ tricks..

No need to do show table...

Directly use delete table....

Delete TBL_Name

.IF ERRORCODE = 3807 THEN .GOTO SomeLBL; /*if table does not exist fair enuf*/

.IF ERRORCODE = 0 THEN .GOTO NextLBL; /*table existed so got errorcode zero*/

Re: Help with ERRORCODE.

well, thanks for the answer, but the problem is kind of different than that. lets see.

echo "DATABASE mydatabase;" >> ${file_sql}
echo "SHOW TABLE ${TABLA2};" >> ${file_sql}
echo ".IF ERRORCODE = 0 THEN .GOTO step0;" >> ${file_sql}
#echo ".QUIT 0;" >> ${file_sql}
echo ".IF ERRORCODE <> 0 THEN .GOTO step;" >> ${file_sql}
echo ".LABEL step0;" >> ${file_sql}
echo "DROP TABLE ${TABLA2};" >> ${file_sql}
echo ".QUIT 0;" >> ${file_sql}
echo ".LABEL step;" >> ${file_sql}
echo "SHOW TABLE ${TABLA1};" >> ${file_sql}
echo ".IF ERRORCODE = 0 THEN .GOTO step2;" >> ${file_sql}
echo ".IF ERRORCODE <> 0 THEN .GOTO step3;" >> ${file_sql}
echo ".LABEL step2;" >> ${file_sql}
echo "RENAME TABLE ${TABLA1} TO ${TABLA2};">> ${file_sql}

the purpose of the sql, is first ask if tabla2 exist (in the first run it should not exist, because this will be a backup table that i will create in the last line of the code), then the show table should send some answer like 0 or not 0 values, instead of that the show table says "error 3807 table doesnt exist statement=1 info=0;" and that error just damage the all process because i can't get the condition i want.

i just want to get a value after i validate if the table exist.. but I don't know how to do that.. that's why I use the "".IF ERRORCODE = 0 " and the "".IF ERRORCODE <> 0 " question to get the values.. but it doesnt work just how I want.

please contact me at any time.. i will be connected.
Enthusiast

Re: Help with ERRORCODE.

You can handle the errors in Unix by entering the below command:

> echo $?

This would return 0 if successful and some other number > 0 if unsuccessful.

The above piece of code looks like you are trying to append to file_sql. Guess, you would have to use bteq and pass file_sql and then check for the return status using the above command.