ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

IBM i Access Client Solutions (ACS) Run SQL Scripts - Print/Display variable

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • IBM i Access Client Solutions (ACS) Run SQL Scripts - Print/Display variable

    Hi,

    Is there an easy way to display/print a variable on the output of the IBM i ACS Run SQL Script screen?
    For having a quick check when writing a script it would very helpful to be able to display the content of a variable.
    E.g. in below script, I want to know the value of var_locks. I tried using PRINT var_locks but that does not work.


    Code:
    BEGIN
    
    -- DECLARE Variables
    
    DECLARE lib_prefix1 VARCHAR(3);
    DECLARE var_locks INT;
    DECLARE var_flag VARCHAR(10);
    
    ----------------------------
    
    -- Check Locks
    SET var_locks = 0;
    SET var_locks = var_locks + (SELECT count(*) FROM QSYS2.OBJECT_LOCK_INFO WHERE SYSTEM_OBJECT_SCHEMA = 'LIBA' AND SYSTEM_OBJECT_NAME = 'FILE1' AND OBJECT_TYPE = '*FILE');
    SET var_locks = var_locks + (SELECT count(*) FROM QSYS2.OBJECT_LOCK_INFO WHERE SYSTEM_OBJECT_SCHEMA = 'LIBB' AND SYSTEM_OBJECT_NAME = 'FILE2' AND OBJECT_TYPE = '*FILE');
    SET var_locks = var_locks + (SELECT count(*) FROM QSYS2.OBJECT_LOCK_INFO WHERE SYSTEM_OBJECT_SCHEMA = 'LIBC' AND SYSTEM_OBJECT_NAME = 'FILE3' AND OBJECT_TYPE = '*FILE');
    
    IF (var_locks > 0) THEN
    SET var_flag = 'LOCKS';
    
    ELSE
    SET var_flag = 'NOLOCKS';
    
    END IF;
    
    >-- Code for check var_locks contents here <--
    
    END;

  • #2
    You can use LPRINTF to write the value of a variable into the job log, then look at the job log from the View menu.



    SQL is the one tool I cannot work without. Take it away from me and I’ll start driving a truck for a living. Naturally I’m eager to find more ways to make SQL work for me. Today I’d like to share how I recently used SQL to write a huge CL command for me. This

    Comment


    • #3
      Hi Ted, thanks for your reply. However LPRINTF is not found in library systools. Is there really no other way?

      Comment


      • #4
        There may be an easier way, but this is one way:
        Code:
        [I]DROP TABLE IF EXISTS session.locks;
        DECLARE GLOBAL TEMPORARY TABLE session.locks (locknum int);[/I]
        
        BEGIN
        
        -- DECLARE Variables
        
        DECLARE lib_prefix1 VARCHAR(3);
        DECLARE var_locks INT;
        DECLARE var_flag VARCHAR(10);
        
        ----------------------------
        
        -- Check Locks
        SET var_locks = 0;
        SET var_locks = var_locks + (SELECT count(*) FROM QSYS2.OBJECT_LOCK_INFO WHERE SYSTEM_OBJECT_SCHEMA = 'LIBA' AND SYSTEM_OBJECT_NAME = 'FILE1' AND OBJECT_TYPE = '*FILE');
        SET var_locks = var_locks + (SELECT count(*) FROM QSYS2.OBJECT_LOCK_INFO WHERE SYSTEM_OBJECT_SCHEMA = 'LIBB' AND SYSTEM_OBJECT_NAME = 'FILE2' AND OBJECT_TYPE = '*FILE');
        SET var_locks = var_locks + (SELECT count(*) FROM QSYS2.OBJECT_LOCK_INFO WHERE SYSTEM_OBJECT_SCHEMA = 'LIBC' AND SYSTEM_OBJECT_NAME = 'FILE3' AND OBJECT_TYPE = '*FILE');
        
        IF (var_locks > 0) THEN
        SET var_flag = 'LOCKS';
        
        ELSE
        SET var_flag = 'NOLOCKS';
        
        END IF;
        
        >-- Code for check var_locks contents here <--
        [I]INSERT INTO session.locks VALUES(var_locks);[/I]
        
        END;
        
        [I]SELECT * FROM session.locks;[/I]

        Comment

        Working...
        X