Sunday, 10 September 2017

AS400 Training for beginners-DB2 400

Prepared by: Malleswara Rao                
    DB2/400 (Database )               
                                                          
1.SIMPLE PHYSICAL FILE
        *************** Beginning of data ************
0001.00      A          R STUDET1                     
0002.00      A            STUNO          5S 0        
0003.00      A            STUNAME       10A          
0004.00      A            STUDEPT        5A          
0005.00      A          K STUNO                      
        ****************** End of data ***************
OUTPUT:

         STUNO   STUNAME     STUDEPT      
 000001    111   aaa          cse         
 000002    222   bbb          it          
 000003    333   ccc          ece         
 000004    444   ddd          eee          
 000005    555   eee          mca         
 000006 1,248   mahesh       mca         
 ****** ******** End of report ******** 


2.PHYSICAL FILE WITH UNIQUE, COLHDG, DATFMT  KEY WORD

        *************** Beginning of data ******************************************
0001.00      A                                      UNIQUE                         
0002.00      A          R FORMAT                                                   
0003.00      A            EMPNO          5S 0                                       
0004.00      A            EMPNAME        5A         COLHDG('EMPNAME1')             
0005.00      A            EMPSAL         5S 0                                      
0006.00      A            EMPD            L         DATFMT(*MDY)                   
0007.00      A          K EMPD                                                     
        ****************** End of data *********************************************



OUTPUT:

         EMPNO   EMPNAME1  EMPSAL   EMPD     
 000001    101    gopi          1   01/01/40 
 000002    102    naga          2   09/09/09 
 000003      2    moa          23   02/01/40 
 ****** ******** End of report ********    


LOGICAL FILE:

3. SINGLE RECORD FORMATS NON JOIN LOGICAL FILE:

        *************** Beginning of data ***********************************
0002.00      A          R FRMT                      PFILE(EXMPLE1)          
0003.00      A            ENO                                               
0004.00      A            ENAME                                             
0005.00      A          K ENO                                               
        ****************** End of data **************************************

OUTPUT:

       EMP NUMBER  EMP NAME  
000001      111    aaa       
000002      222    bbb       
000003      333    ccc       
000004      444    ddd       
000005      555    eee       
000006      123    abc       

LOGICAL FILE WITH SELECT / OMIT KEY WORDS:

        *************** Beginning of data *************************************************
0000.01                                             DYNSLT                                
0001.00      A          R STUDET                    PFILE(STUDET)                         
0002.00      A            STUNO                                                           
0003.00      A            STUNAME                                                         
0004.00      A          K STUNO                                                            
0005.00      A*         S STUNO                     COMP(GT 300)                          
0006.00      A          O STUNO                     COMP (GT 400)                          
        ****************** End of data ****************************************************

OUTPUT:           

   STUNO   STUNAME          
000001           111   AAA              
000002           222   BBB              
000003           333   CCC              
****** ********  End of report  ********



MULTIPLE RECORD FORMATS NON JOIN LOGICAL FILES:

        *************** Beginning of data **********************************
0001.00      A          R FORMAT                    PFILE(EMP)             
0002.00      A            EMPNO                                             
0003.00      A            EMPNAME                                          
0004.00      A            EMPSAL                                           
0005.00      A          K EMPNO                                            
0006.00      A          R FORMAT1                   PFILE(BOSS)            
0007.00      A            EMPNO                                            
0008.00      A            EMPNAME                                          
0009.00      A            EMPSAL                                            
0010.00      A          K EMPSAL                                           
        ****************** End of data *************************************

OUTPUT:
         EMPNO   EMPNAME  EMPSAL             
 000001    101    gopi         1             
 000002    102    naga         2             
 000003    103    moss         3             
 000004    104    boss         4             
 000005    105    vishn        5             
 ****** ********  End of report  ********    



JOIN LOGICAL FILES:

  1. Inner Join / Natural Logical File

        *************** Beginning of data *****************************************
0001.00      A          R RINFO                     JFILE(CORS STUDENT)           
0002.00      A          J                           JOIN(CORS STUDENT)            
0003.00      A                                      JFLD(CCD CCD)                 
0004.00      A            CCD                       JREF(STUDENT)                 
0005.00      A            CNM                                                     
0006.00      A            FEES                                                    
0007.00      A            ROLL                                                    
0008.00      A            SNAME                                                   
        ****************** End of data ********************************************

OUTPUT

              CCD   CNM    FEES   ROLL   SNAME       
 000001 100   COMP   10000     1          AAA         
 000002 200   AS400   30000     2          BBB         
 000003 300   JAVA    10000     3        CCCC        
 000004 400   VB         20000     4         DDD         
 ****** ********  End of report  ********    

  1. Left Outer Join  Logical File

        *************** Beginning of data *******************************************
0000.01      A                                      JDFTVAL                         
0001.00      A          R RINFO                     JFILE(STUDENT CORS)              
0002.00      A          J                           JOIN(STUDENT CORS)              
0003.00      A                                      JFLD(CCD CCD)                   
0004.00      A            CCD                       JREF(CORS)                       
0005.00      A            CNM                                                       
0006.00      A            FEES                                                      
0007.00      A            ROLL                                                           
0008.00      A            SNAME                                                     
        ****************** End of data **********************************************
    

            OUTPUT

              CCD   CNM    FEES   ROLL     SNAME       
 000001 100   COMP   10000     1            AAA        
 000002 200   AS400    30000     2          BBB        
 000003 300   JAVA   10000       3         CCCC       
 000004 400   VB         20000      4         DDD        
 000005   0                                  5         EEEE       
 000006   0                                    6         FFF        
 ****** ********  End of report  ********     



  1. Self Join Logical File:
PF:

        *************** Beginning of data *****************************
0001.00      A          R RCD                                          
0002.00      A            EMPNO          5S 0                         
0003.00      A            EMPNAME        5A                           
0004.00      A            EMPSAL         5S 0                         
        ****************** End of data ********************************
OUTPUT

         EMPNO   EMPNAME  EMPSAL            
 000001    101    AAA        111            
 000002    102    BBB        222            
 000003    103    CCC        333             
 000004    104    DDD        444            
 000005    105    EEE        555            
 000006    106    fff        106            
 000007    107    ggg        107            
 ****** ********  End of report  ********   




Self Join Logical File:
        *************** Beginning of data **********************************************
0001.00      A*                                     JDFTVAL                            
0002.00      A          R JOINREC                   JFILE(PF48 PF48)                   
0003.00      A          J                           JOIN(1 2)                          
0004.00      A                                      JFLD(EMPNO EMPSAL)                 
0005.00      A            EMPNO                     JREF(1)                            
0006.00      A            EMPSAL                    JREF(1)                            
        ****************** End of data *************************************************

OUTPUT

        EMPNO   EMPSAL                     
000001    106      106                    
000002    107      107                    
****** ********  End of report  ********  


No comments:

Post a Comment