DB2 and QMF

 

  SQLCODES:
+100 row not found
+464 Stored procedure completed successfully, but exceeded the defined limit of result sets
+466 Stored procedure completed successfully
+494 Stored procedure completed successfully, but number of result sets > number of locators
   
-180 DATETIME value has invalid syntax
-181 DATETIME value is not valid
-203 reference to column is ambiguous
-205 colname is not a column in this table
-305 null value can't be assigned to host variable because no null indic
-530 referential integ error - insert / update value of foreign key constraint is invalid
-534 primary key can't be updated because there are multiple rows involved
-545 table check constraint
-551 not authorized to do this function
-803 duplicate insert
-805 program-name is not in plan-table
-811 result of embedded select or subquery > 1 row
-817 insert/updte/delete can't be executed .. ims trans is inquiry only
-818 timestamp error
-904 unavailable resource
-911 rollback has occurred due to timeout or deadlock
-913 unsuccessful execute due to timeout or deadlock - no rollback
-922 connect authorization failure
-923 connection not established
-924 DB2 connect internal error
DATE FORMATS:
USA mm/dd/ccyy
ISO ccyy-mm-dd
Europe dd.mm.ccyy
TIME FORMATS:
USA hh.mm   am/pm
ISO hh.mm.ss
Europe hh.mm.ss
Timestamp ccyy-mm-dd-hh.mm.ss.mmmmmm  '2017-03-17-01.01.01.000001'
ccyy-mm-dd hh:mm:ss.mmmmmm  '2017-03-17 01:01:01.000001'
ccyy-mm-dd hh.mm.ss.mmmmmm  '2017-03-17 01.01.01.000001'
QMF FORMS - USAGE:
count number of rows
break separates result table data when value in column changes
sum adds values in column of result table
group
across displays data horizontally on result table for grouped data
QMF FORMS EDIT:
C Char field - No change of  display value
CW Char field - Wrap data
CT Char field - Wrap data, break on space
Lx Numeric field - Neg sign
Kx Numeric field - Neg sign, commas
Dx Numeric field - Neg sign, commas, dollar sign
Ix Numeric field - Neg sign, leading zeroes
Jx Numeric field - Leading zeroes
Px Numeric field - Neg sign, commas, per cent sign
x Number of decimal places (0 or none if none)
TDYx Date field - Year first      2001-01-15  (4 digit year)
TDMx Date field - Month first    01-15-2001
TDDx Date field - Day first       15-01-2001
TDYAx Date field - Year first      20-01-15     (2 digit year)
TDMAx Date field - Month first    01-15-01
TDDAx Date field - Day first       15-01-01
x . or / or -
TTSx Time field - ISO, European       13.25.10
TTU: Time field - USA format           01:25 PM
TTC: Time field - Hr, min, sec, delim   01:25:10
TTAx Time field - Hr, min (24 hr)       13.25
TTAN Time field - Hr, min (24 hr)       1325 (no delim)
x any special char including blank (no letters or digits)
  FUNCTIONS:
Distinct  
Min minimum
Sum total
Avg average
Max highest
Count number of rows
Select SUM(salary), MIN(salary), AVG(salary), MAX(salary), COUNT(*) from tbl1 where
Day day part of date value
Dayname name of day
Dayofweek 1-7 (1=sunday)
Dayofweek_iso  1-7 (1=monday)
Dayofyear 1-366
days integer representation of date
Julian_day integer # of days from 01/01/4712 BC
Lcase/Lower chars converted to lower case
Ltrim chars w/leading blanks removed
Monthname char string with month name
Rtrim chars w/trailing blanks removed
Ucase/Upper chars converted to upper case
Null Indic

variable defined in work storage as a smallint - follows variable name in select stmt
(-1=null, -2=char or numeric conversion)

Char fixed length (pic x)
Varchar variable length (pic x)
Deci decimal
Integer fullword binary integer
Smallint 1/2 word binary integer
Sqlca sqlcode, sqlwarning, sqlcaid, sqlcabc, sqlerrm
Commit closes cursors depending on relation of DB
Correlation
Name
qualifies a tablename in from clause ("A")
Correlate
Reference

reference within a subquery to a column  of a table identified at a higher level

Host Variable data element ":"
Host Structure data group level  ":"
Exists

tests for existence of certain rows, if there, result is true WHERE [not] EXISTS (select * from TBL1 where ....)

Referential
Integrity
relationships between multiple columns within one or several  tables must be kept by use of constraints (rules) defined in DB2 catalog
SYNTAX:
Update TBL1 set A = "_" where C = "_" (where current of cursor)
Insert into TBL1 values ("_", "_", "_")
Delete from TBL1 where ...
Alter TBL1 add E char(5)
Select Year (current_date - birthdate) from EMP = age in years
Select Distinct dept from TBL1
Group By

column1, column2

Having group (not column) must have value 'x'
View create view1 as select A, B, C from TBL1 where....
For Fetch Only tells DB2 there is no update
Fetch First n
Rows Only
retrieve limited number of rows
Value select value(A,'unknown') = "unknown" if null
Coalesce similar to VALUE
Select ('1993-08-25' - hiredate) = timespan between the two dates
Substr

select substr(A,1,1) || "." || B  into :hostvar from tbl1 where  hostvar-name = "xxx"

SYSIBM TABLES:
Syscolauth Records the UPDATE privileges held by users on individual columns of a table or view
Syscolumns Contains one row for every column of each table and view,
                                     including the columns of the DB2 catalog tables
 
Syscopy

Contains information needed for recovery

Sysdatabase Contains one row for each database, except for database DSNDB01
Sysdbauth Records the privileges held by users over databases
Sysdbrm Contains one row for each DBRM of each application plan
Sysfields Contains one row for every column that has a field procedure
Sysforeignkeys Contains one row for every column of every foreign key
Sysindexes Contains one row for every index, including indexes on catalog tables
Sysindexpart Contains one row for each unpartitioned index and one row for each partition of a partitioned index
Syskeys Contains one row for each column of an index key
Syslinks Contains one row for every link between tables.  (In each link, the parent and children are catalog tables)
Sysplan Contains one row for each application plan
Sysplanauth Records the privileges held by users over application plans
Sysplanep

Records the dependencies of plans on tables, views, synonyms, table spaces, and indexes

Sysrels Contains one row for every link
Sysresauth Records the privileges held by users over buffer pools, storage groups and table spaces
Sysstmt

Contains one or more rows for each SQL statement of each DBRM

Sysstogroup Contains one row for each storage group
Syssynonyms Contains one row for each synonym of a table or view
Systabauth Records the privileges held by users on tables and views
Systablepart Contains one row for each unpartitioned table space and one row for each partition of a partitioned table space
Systables Contains one row for each table or view
Systablespace Contains one row for each table space
Sysuserauth Records the system privileges held by users
Sysviewdep Records the dependencies of views on tables and other views
Sysviews Contains one or more rows for each view
Sysvltree Contains the remaining part, if any, of the parse tree representation of views (see SYSVTREE)
Sysvolumes Contains one row for each volume of each storage group
Sysvtree Contains a row for each view.  Each row contains the parse tree of the view.  If parse tree is longer than 4000 bytes, the rest of the parse tree is saved in the SYSVLTREE table.
MISC:
Join

Retrieve data from multiple tables based on match of column values
    (Select A, B, C from TBL1, TBL2 where... order by ...)

Inner Join selects rows with equal values
Left Outer Join selects rows with equal values and rows from left table
Right Outer Join selects rows with equal values and rows from right table
Full Outer Join  selects all rows from all tables
Union

Combine result tables from multiple selects and eliminate duplicates
                (Select A, B from TBL1 where ... UNION select A, B from TBL2 where ...)

Union All Same as union but retaining the duplicates
Index set of pointers logically ordered by the values of the key
Package object containing sql stmts that have been bound statically
CS Cursor Stability - only locks uncommitted changes and current row
RR Repeatable Read - locks all rows read until commit is executed
UR Uncommitted Read - can only be used if result table is read only
Optomize For set performance to be best when X rows or less are returned
Display sqlerrd(3) shows number of rows affected in last sql executed

 

If null-indic is not declared for column that can contain nulls and select returns null value, -305 is issued at execution

 

To pass parms to qmf batch,

SYSTSIN DD *
ispstart pgm (dsqqmfe) newappl(dsqe) -
parm (m=b, s=dsnt, i=procname(&&parm_value))

SYSTSIN   dsn system(.)   ikjeft01

 

BIND   plan (planname)
            owner (ownername)
            member (memname)
            action (add/replace) -
            iso (cs/rr) -
            validate (bind) -
            explain (yes/no)

 

 SELECT COL1
            , COL2
            , CASE COL3
                 WHEN '123' THEN 'ABC'
                 WHEN '234' THEN 'BCD'
                 ELSE                       'EFG'
            END

 FROM TBL1

 SELECT ss_no,     SUBSTR(lst_nm,1,1)     ||
 RTRIM(LOWER(SUBSTR(lst_nm,2)))    || ', ' ||
                           SUBSTR(frst_nm,1,1)  ||
 RTRIM(LOWER(SUBSTR(frst_nm,2))) as name
FROM FC
WHERE ss_no = '123456789'
AND curr_aplbl_dt = '9999-12-31'
 

SELECT DISTINCT MONTH(MAX(wk_dt)) as month
                                   , DAY(MAX(wk_dt)) as day
                                 , YEAR(MAX(wk_dt)) as year
                                , WEEK(MAX(wk_dt)) as week
                     , DAYOFYEAR(MAX(wk_dt)) as "day of year"
FROM ARS;
 

SELECT  VALUE(ANN.ss_no,AAA.ss_no)                   

           ,  VALUE(ANN.main_ofc_no,AAA.main_ofc_no)       

           ,  VALUE(ANN.fc_no,AAA.fc_no)                   

           ,  VALUE(ANN.fc_paym_cd,AAA.fc_paym_cd)         

          ,  VALUE(ANN.anu_rvn_src_lne_no,AAA.anu_rvn_src_lne_no)

FROM                                                 

          (SELECT B.main_ofc_no, A.ss_no, A.fc_no, A.fc_paym_cd, A.anu_rvn_src_lne_no         

           FROM ANN A                                    

                   ,  ROL B                                    

           WHERE A.wk_dt  >  '2000-12-29'                     

            AND  A.wk_dt <=  '2001-04-06'                     

            AND  B.wk_dt  =  '2001-04-06'                     

            AND  A.ofc_no =   B.ofc_no  )   ANN               

 FULL OUTER JOIN                                      

          (SELECT B.main_ofc_no, A.ss_no, A.fc_no, A.fc_paym_cd, A.anu_rvn_src_lne_no         

            FROM AAA A                                    

                     , ROL B                                    

           WHERE A.wk_dt  =  '2001-04-06'                     

            AND  B.wk_dt  =  '2001-04-06'                     

            AND  A.ofc_no =   B.ofc_no  )   AAA                

  ON   ANN.ss_no              = AAA.ss_no       AND  

          ANN.main_ofc_no    = AAA.main_ofc_no AND  

          ANN.fc_no              = AAA.fc_no       AND  

          ANN.anu_rvn_src_lne_no = AAA.anu_rvn_src_lne_no

  GROUP BY   ANN.ss_no,     AAA.ss_no,    ANN.main_ofc_no,     AAA.main_ofc_no
                ,    ANN.fc_no,     AAA.fc_no,   ANN.fc_paym_cd ,     AAA.fc_paym_cd                        

               ,     ANN.anu_rvn_src_lne_no,     AAA.anu_rvn_src_lne_no;


 

Select A, B

From T1

      ,  T2

      ,    (Select D, C as THE_C  From T3  Group by D)   AS T4;

 

Select A, B, C

From  T1

Where ....

 And    EXISTS (Select X, Y, Z  From T2 where....);

 

SELECT DISTINCT(ofc_no)         

FROM    ROL         

WHERE  wk_dt < '2000-12-01'     

 AND     ofc_no NOT  IN (SELECT DISTINCT(ofc_no) 

                                        FROM ROL              

                                        WHERE wk_dt = (SELECT MAX(wk_dt) FROM ROL));

 

SELECT *

FROM   WFC
WHERE curr_aplbl_dt = '9999-12-31'
AND      wk_dt = &wkdt
AND     (emple_sta_cd LIKE '%0' OR
             emple_sta_cd LIKE '%2' OR
             emple_sta_cd LIKE '%5')
AND      ofc_no IN (SELECT DISTINCT ofc_no
                                           FROM ORG
                                           WHERE curr_aplbl_dt = '9999-12-31'
                                           AND ofc_cnmk_rpt_in = 'Y');

 

SELECT LENGTH(TRIM (colname))

FROM tblname


DB2
CICS
IMS
MQ
Stored Procedures
Resume


HOME