Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Regd Native SQL's

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Raghuraman_New

New User


Joined: 03 Oct 2008
Posts: 43
Location: Chennai,India.

PostPosted: Mon Nov 12, 2012 9:31 pm    Post subject: Regd Native SQL's
Reply with quote

Hi,
I am trying to write a new Native SQL stored Procedure. I would like to know whether there is any option for declaring the Fixed-length tables or variable length tables like we do in Cobol.

Code:
DECLARE PV_SQL_CODE          INTEGER DEFAULT 0;             
DECLARE PV_SQL_STATE         CHAR(05) DEFAULT '00000';     
DECLARE PV_MSG_TX            VARCHAR(200) DEFAULT 'NO MSG';
                                                           

To use the array in my Native SQL, i have used the alternate solution like below,
Code:

              IF IN_CNTC_AFFL_CNT_1 > 1 THEN                         
                 MOVE_LOOP: LOOP                                     
                   IF IN_CNTC_AFFL_INFO_1 > SPACES THEN             
                     SET IN_ACTION_CD =                             
                                   SUBSTR(IN_CNTC_AFFL_INFO_1,1,1); 
                     SET IN_STRUC_ID =                               
                                  SUBSTR(IN_CNTC_AFFL_INFO_1,2,12); 
                     SET IN_NODE_ID =                               
                                 SUBSTR(IN_CNTC_AFFL_INFO_1,14,20); 
                     SET IN_NODE_VAL_IND =                           
                                 SUBSTR(IN_CNTC_AFFL_INFO_1,34,01); 
                     SET IN_NODE_VAL_TEXT =                         
                                SUBSTR(IN_CNTC_AFFL_INFO_1,35,30);   
                     SET IN_DELIMTR =                               
                                  SUBSTR(IN_CNTC_AFFL_INFO_1,65,1); 
                   ELSE                                             
                     SET PV_MSG_TX     = 'INVALID ADDRESS';         
                     SIGNAL SQLSTATE '75001';                       
                   END IF;                                           
         SET PV_INT_CNT = PV_INT_CNT + 1;                   
         IF IN_CNTC_AFFL_CNT_1 = PV_INT_CNT THEN           
            LEAVE MOVE_LOOP;                               
         END IF;                                           
      END LOOP MOVE_LOOP;                                   
    ELSE                                                   
      SET IN_ACTION_CD = SUBSTR(IN_CNTC_AFFL_INFO_1,1,1);   
      SET IN_STRUC_ID = SUBSTR(IN_CNTC_AFFL_INFO_1,2,12);   
      SET IN_NODE_ID = SUBSTR(IN_CNTC_AFFL_INFO_1,14,20);   
      SET IN_NODE_VAL_IND =                                 
                       SUBSTR(IN_CNTC_AFFL_INFO_1,34,01);   
      SET IN_NODE_VAL_TEXT =                               
                      SUBSTR(IN_CNTC_AFFL_INFO_1,35,30);   
      SET IN_DELIMTR = SUBSTR(IN_CNTC_AFFL_INFO_1,65,1);   
                                                           
    END IF;               



But I need to know whether in the Native SQL procedure, can i declare like fixed length tables or variable length tables. do we have a option to do like that.. ?? Please advise
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Nov 13, 2012 11:46 am    Post subject:
Reply with quote

Hi Raghu,

Quote:
I would like to know whether there is any option for declaring the Fixed-length tables or variable length tables like we do in Cobol.

Are you talking about arrays, if so, you have to wait till V11 which supports SQL PL ARRAY

Meanwhile you can look into DGTT(Declare Global Temporary Tables).

Thanks,
Sushanth
Back to top
View user's profile Send private message
Raghuraman_New

New User


Joined: 03 Oct 2008
Posts: 43
Location: Chennai,India.

PostPosted: Wed Nov 14, 2012 12:00 pm    Post subject: Reply to: Regd Native SQL's
Reply with quote

Thank you Sushanth!!!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Regd Date Functions in Syncsort muralikrishnan_new SYNCSORT 2 Fri Jul 01, 2016 8:21 pm
No new posts Exception Handling in Native Stored P... Mariraj DB2 1 Thu Feb 25, 2016 4:08 am
No new posts Debugging Native Stored procedure in ... paramoberoi IBM Tools 0 Wed Dec 23, 2015 11:55 am
No new posts native db2 Stored procedures on Mainf... HABBIE DB2 2 Tue Aug 04, 2015 1:54 pm
No new posts Native stored procedures on IMS DB HABBIE IMS DB/DC 3 Wed Jul 29, 2015 10:41 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us