Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    2

    Smile Unanswered: Temp table inside SP

    I need to know if a given sp is using temp table without the keyword "with no log", I tried to write the following;


    select count(*)
    from sysprocbody s
    where s.procid = (select procid from sysprocedures where lower(trim(procname)) = "test_sp")
    and s.datakey = "T"
    and (s.data like "%into%temp%" or s.data like "%create%temp%")
    and not exists (Select * from sysprocbody where datakey = "T" and data like "%with%no%log" and procid = s.procid)

    but the problem here is that if test_sp uses 2 or more temp table and in one of the temp table created he uses the keyword "with no log" it should return 1(to tell me that a temp table without with no log is created).

    I would love to have this done in plain sql/sp if posible.

    Thanks,
    Aris

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    What do you want to accomplish with it? Do you want to use the query inside an application for or do you just want to check out one time only which SP's create these tables?
    Because for the latter maybe you'd better use the dbschema utility to check it out. In a UNIX environment for example like:
    Code:
    dbschema -d database -f all | \
    egrep -i 'into temp|create temp' | grep -i 'with no log'
    In a DOS environment without PERL or grep for Windows it will be a bit more difficult.

    But since you preferred a SQL query I think the only thing that can be done is to use a stored procedure to count the number of create/into statements and the number of 'with no log's. I can give the idea how I probably would do it, but that'll be in my next post, cause first I must log off to get something through VPN.

  3. #3
    Join Date
    Sep 2006
    Posts
    2
    I need to put this inside an application(probably creating an SP is better).
    I am currently creating an application that will monitor all the object being checkedin by the developers to see if they are following the standards that we put in place.

    Thanks in advance for helping me on this.

    Aris

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, here's the solution I had in mind. It's not impeccable though, unless you change the "source LVARCHAR" declaration into "source CHAR(x)" where x is the maximum length of the concatenated datakey 'T' data columns for each relevant stored procedure.
    Code:
    CREATE FUNCTION "informix".cnt(search LVARCHAR, source LVARCHAR)
        RETURNING SMALLINT;
    
        DEFINE i        SMALLINT;
        DEFINE j        SMALLINT;
        DEFINE l1       SMALLINT;
        DEFINE l2       SMALLINT;
        DEFINE nr	    SMALLINT;
    
        LET nr = 0;
        LET l1 = LENGTH(search);
        LET l2 = LENGTH(source);
        IF l1 > 0 AND l2 >= l1 THEN
            FOR i IN (1 TO l2)
                IF substr(search,1,1) = substr(source,i,1) THEN
                    LET j = 2;
                    IF l1 > 1 THEN
                        IF l1 - 1 > l2 - i THEN
                            RETURN nr;
                        END IF
                        WHILE j <= l1
                            IF substr(search,j,1) != substr(source,i+j-1,1) THEN
                                EXIT WHILE;
                            END IF
                            LET j = j + 1;
                        END WHILE
                    END IF
                    IF j > l1 THEN
                        LET nr = nr + 1;
                        LET i = i + j - 2;
                    END IF
                END IF
            END FOR
        END IF
        RETURN nr;
    
    END FUNCTION
    DOCUMENT '';
    Don't know whether this will work absolutely well; I altered there original a little, which was meant to return the first position of a substr in a string. It runs on an IDS 9.2 server and maybe the builtin "substr" function doesn't exist in an IDS 7.x, don't know right now.
    But inside a query like yours this stored function can be invoked like:
    Code:
    AND cnt("temp",s.data) > cnt("with no log",s.data)
    As I said there has to be done something more with the 'data' columns since usually stored procedures are written in more than one datarecord (of 256 bytes) in sysprocbody.

    Hope this gives any useful hints anyway,
    regards

    BTW: If you're creating the application in a language with the ability of dynamical memory allocation for variables I think you should build a routine like this one in that application. Everything can be fixed then in a simple way.
    Last edited by Tyveleyn; 09-07-06 at 05:38.

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Tyveleyn
    What do you want to accomplish with it? Do you want to use the query inside an application for or do you just want to check out one time only which SP's create these tables?
    Because for the latter maybe you'd better use the dbschema utility to check it out. In a UNIX environment for example like:
    Code:
    dbschema -d database -f all | \
    egrep -i 'into temp|create temp' | grep -i 'with no log'
    In a DOS environment without PERL or grep for Windows it will be a bit more difficult.

    But since you preferred a SQL query I think the only thing that can be done is to use a stored procedure to count the number of create/into statements and the number of 'with no log's. I can give the idea how I probably would do it, but that'll be in my next post, cause first I must log off to get something through VPN.
    With DOS you can pipe | find or a findstr.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •