Wednesday, January 6, 2016

SAS in Mainframes(z/Os) - Connecting to DB2 from SAS in mainframes

This post is all about connecting DB2 from SAS in mainframe environment and using the result set for further processing. To call and connect to DB2 session we need the SQL proc to be called first using the statement PROC SQL. When used in a program we need to begin  the code with PROC SQL and end it with QUIT; In Between these two we can write our SQL codes to fetch the required data.
Without wasting much time lets check the first JCL used to connect to db2.



//TTYYTST  JOB(TEST),CLASS=I,MSGCLASS=X,MSGLEVEL=(1,1)
//                NOTIFY=&SYSUID
//STEP01  EXEC PROC=SAS,OPTIONS='  '
//WORK    DD SPACE=(CYL,(,500)),VOL=(,,,59)
//OUTFILE DD DSN=TEST.OUTPUT.SAS,
//                   DISP=(,CATLG,DELETE),
//                  DCB=(RECFM=FB,LRECL=122,BLKSIZE=000),
//                  UNIT=SYSDA,SPACE=(TRK,(500,500),RLSE)
//SYSIN     DD *
OPTION NOCENTER;
OPTION SORTLIB;
PROC SQL;                                     <== Starts here
CONNECT TO DB2 (SSID=DQAT);    <== DQAT is the db2 subsystem here  
   CREATE TABLE test AS                  <== test is the sas datset here.
        SELECT * 
         FROM CONNECTION TO DB2 (
select fname,
         lastname,
         dept_name,        
from  test01.dept
where student_id = 1 fetch first 5 rows only) ;

%PUT SQLXRC=&SQLXRC;
%PUT SQLMSG=&SQLMSG;
QUIT;

DATA STEP1;
SET TEST;
FILE OUTFILE DLM=',' ;
IF _N_=1 THEN DO;
PUT   'FIRST NAME,'
          'LAST NAME,'
          'DEPT NAME,'
;
END;
PUT  FNAME
          LASTNAME
         DEPT_NAME
;
//SYSPRINT DD SYSOUT=*
//*

JCL is self explanatory. %PUT SQLXRC and SQLMSG are the macros used to capture the DB2 return codes and error messages if any.

3 comments:

  1. I Have a query related to a SAS pgm im using which is failing to extract records from few sas datasets but extracts for 1 dataset .

    NOTE: 116 records were written to the file RCPO2.
    NOTE: There were 116 observations read from the data set RCDATA.W01_PO2.
    NOTE: There were 0 observations read from the data set RCDATA.W02_PO2.
    NOTE: There were 0 observations read from the data set RCDATA.W03_PO2.
    NOTE: There were 0 observations read from the data set RCDATA.W04_PO2.
    NOTE: There were 0 observations read from the data set RCDATA.W05_PO2.

    ReplyDelete
    Replies
    1. Hi,
      I would like to know more about your issue and your program. Looking into the observations is not sufficient.
      To me it looks like the criteria to extract the records is failing, and hence it is not fetching any records.
      There is no error in the program.
      Check the condition based on which you are pulling the data. Its not failing. Also check for any érror' in the SASLOG, if any previous step has any error.

      Delete
  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh Srivastava
    MaxMunus
    E-mail: saurabh@maxmunus.com
    Skype id: saurabhmaxmunus
    Ph:+91 8553576305 / 080 - 41103383
    http://www.maxmunus.com/


    ReplyDelete