Ad

Teradata BTEQ : IF Conditon , Validate SP Compilation With Runtime Actual Parameters & Export DDL

BTEQ related questions - I am compiling a list of SP's via BTEQ like this

.compile file=sp1 ; 
.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;
 compile file=sp2 ; 
.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;
.logoff
 .quit

.LABEL  SQLERROR

  .logoff ;

  .quit ;

I have the following Q's regarding these

  • If The SP compilation fails, the following .IF condition, should catch it and rest of the file should be ignored as per what the .LABEL , SQLERROR says
    But that does not happen

    .compile file=sp1 ;
    One Error/Warning.
    Warning: 5526 Stored Procedure is not created/replaced due to error(s).
    Total elapsed time was 1 second.
    Errors reported during compilation
    `---------------------------------------------------------------------------
    SPL1076:E(L3), The right parenthesis in parameter declaration is missing.

    +---------+---------+---------+---------+---------+---------+---------+----

    .IF ERRORCODE <> 0 THEN .GOTO SQLERROR; +---------+---------+---------+---------+---------+---------+---------+---- .compile file=sp2 ;

    Procedure has been replaced. Total elapsed time was 1 second.

Why is the IF not working out ?

  • The stored procedure replaces a view. The entire replace stored proc statement is generated via show that is exported . I will come to that later. Inside the SP the code is something like this

    REPLACE PROCEDURE "DB"."TB"
    (IN L_N2 VARCHAR(500),
    IN L_N1 VARCHAR(500)
    )
    BEGIN

    DECLARE returncode INTEGER DEFAULT 0; DECLARE mystat VARCHAR(60000);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET RETURNCODE=1/0; END SET mystat='REPLACE VIEW '||L_N2||'.TBname ( VCol1,Vcol2) As sel col1,col2 From '||L_N1||'.Tbname'; CALL dbc.sysexecsql(mystat);

    END


Here even if Col1, Col2 have an issue e.g. instead of Col1 I'd put Co l1 the SP will compile . Understandably the run-time outcome of the Stored Procedure Call will not be evaluated when its compiled. This leaves a situation where any kind of error in the generated replace view DDL cannot be detected. Is there any way I can get the replace view to validate at the time of SP compilation ?

  • Lastly- regards exporting DDL via BTEQ
    there is an extra space between DB.TB like "db". "tb" now when I do the same show in SQL assistant - all is well- there's no space. I have to write another sed to del the extra space .
    The view and SP code is very long and often on UNIX the code gets split across new lines like
    sel foo_
        bar, foobar,Col.
        tb from db.tb 
    foo_bar is split across multiple lines .
# std BTEQ options are used. The \n is because I am echoing  all this to file
.SET ECHOREQ OFF \n
.set width 500
.set titledashes off \n
.set format off \n
.set rtitle '' \n
.export report file="$ph" \n
 show procedure $db.$tb ; \n
.export  reset ;  \n
Ad

Answer

Q1: BTEQ simply ignores any error/warning returned by the DBMS (don't know why). If you want to stop after any error/warning you could simply use ACTIVITYCOUNT instead of ERRORCODE:

.compile file=sp1 ; 
.IF ACTIVITYCOUNT > 0 THEN .GOTO SQLERROR;

If you want to ignore warnings I don't know a reliable way without dropping the SP first:

DROP PROCEDURE whatever;
.compile file=sp1 ; -- creates SP whatever
HELP PROCEDURE whatever ATTR;
.IF ERRORCODE = 5495 THEN .GOTO SQLERROR; -- 5495 = SP doesn't exist

Q2: There's no way to validate Dynamic SQL, it's dynamic as the name implies and unknown to a DBMS before it's actually submitted.

Q3: Whitespace around a period in a qualified name don't result in an error, db . td is happily accepted by the parser.

foo_bar should not be split across lines unless the line is too wide, simply increase the maximum length using .set width 30000;

Ad
source: stackoverflow.com
Ad