Some Qs on Stored Procedure


Some Qs on Stored Procedure

Hi, I have couple of qs on SP. Could you please through some light on them?

1) The RequestText field in DBC.Tables does not show the definition of a SP (NULL); while some SPs show values like "ALTER PROCEDURE '535953444241'xn.'535055F5354414745'xn COMPILE;"

What is the difference? Is there any method by which a SP definitinon will get logged in DBC.Tables?

2) When a SP is changed is it okay to do either of '.compile' OR 'REPLACE PROCEURE'? If so, what is the difference between '.compile' & 'REPLACE PROCEURE'?

3) If a SP has multiple sqls within it, does each sql executed get logged in Dbqlogtbl, when the SP is called? Asking this because, I have seen cases where the individual sqls within the SP logged in Dbqlogtbl, and other cases where only CALL statement being logged and not the sqls within the SP.

Tags (1)
Senior Apprentice

Re: Some Qs on Stored Procedure

Q1: The source code of a SP is never logged to any system table. The ALTER was probably after a upgrade to a new release, there's a script to recreate the existing SPs.

Q2: '.compile filename' is just the way to create a SP in BTEQ, the file must have a single CREATE/REPLACE PROCEDURE.

Q3: There's one row in dbc.QryLogV for the CALL and each query within the SP is also logged in DBQL. All statements submitted from within the SP will share the same "RequestNum" while the "InternalRequestNum" increases.

If some statements were missing maybe it was due to a LIMIT THRESHOLD in BEGIN QUERY LOGGING?