DB2 and SQL
• Terminology – DB2 : Relational database manager fully integrated into the IBMi O/S. • Major difference between IBMi (Power Systems) DB2 and DB2 Mainframe or LUW! – SQL : Structured Query Language (SQL) allows user to define, manipulate, query, and secure data. – DDL : Data Definition Language (DDL) is the portion of SQL that creates, deletes, and alters DB2 database objects. • Objects include schemas, tables, indexes, views, sequences, aliases, triggers, procedures. – DML : Data Manipulation Language (DML) is the portion of SQL that manipulates or controls data • Select, Insert, Update, Delete.
– SQL vs. Traditional Terminology SQL term Traditional file access term Schema. A group of related objects that consists of a Library. A group of related objects that enables the user library, a journal, a journal receiver, an SQL catalog, to find the objects by name. and an optional data dictionary. A schema enables the user to find the objects by name. Another name for a schema is collection. Table. A set of columns and rows. Physical file. A set of records. Row. The horizontal part of a table containing a serial Record. A set of fields. set of columns. Column. The vertical part of a table of one data type. Field. One of more bytes of related information of one data type. View. A subset of columns and rows of one or more Logical file. A subset of fields or records of up to 32 tables. physical files. Index. A collection of data in the columns of a table, Index. A type of logical file. logically arranged in ascending or descending order. Package. An object that contains control structures for SQL package. An object that contains control structures SQL statements to be used by an application server. for SQL statements to be used by an application server. Catalog. A set of tables and views that contain No similar object. However, the Display File Description information about tables, packages, views, indexes, (DSPFD) and Display File Field Description (DSPFFD) and constraints. commands provide some of the same information that querying an SQL catalog provides. * Source: IBM i Information Center – V6R1
Select Statement - Syntax: SELECT column names FROM table or view name WHERE search condition GROUP BY column names HAVING search condition ORDER BY column-name - Example: select sum(CTPMTAMT), ctpmttyp from DLYFILE.CRTRNLOG where year(ctctime)= '2011' and month(ctctime) = '10' group by ctpmttyp order by 1; - Results: -100 Claim -80 MedProvidr -60 Attorney -50 Adjuster -40 Misc -20 Draft 0 35 Credit
Select Statement - Example: select sum(CTPMTAMT), ctpmttyp from DLYFILE.CRTRNLOG where year(ctctime)= '2011' and month(ctctime) = '10' group by ctpmttyp having sum(CTPMTAMT) < -50 order by 1; - Results: -100 Claim -80 MedProvidr -60 Attorney
• RPG Equivalent to previous sql statement: d datetime ds d ctctime s 26A d year s 4s 0 d month s 2s 0 d tctpmtamt s 10 2 init(0) . . reade (year : month : type) crtrnlog; dou %eof crtrnlog; tctpmamnt = tctpmamnt + ctpmtamt; . . . reade (year : month : type) ctrnlog; enddo;
Select Statement, cont. - Table Joins: - Inner Join - Returns all rows that have matching values on the join columns. - Left Outer Join - Returns all rows an inner join would, plus all rows from left table that do not have a match from the right table. - Right Outer Join - Returns all rows an inner join would, plus all rows from right table that do not have a match from the Left table. - Full Join - Returns all rows from both tables that have matching values on the join columns, plus all non- matching rows from both tables. - Exception Join - Returns only the rows from the left table (or right table) that do not have a match on the join columns from the right table (or left table).
Select Statement, cont. - Unions: - Definition: - Take the results of 2 or more sub-selects and combine into 1 full select. - Example: select sq_addtime, sq_errcode, sq_program, left(sq_statmnt,110) from bmsqlerr where SQ_ADDTIME > current timestamp - 24 hours union all select sq_addtime, sq_errcode, sq_program, left(sq_statmnt,110) from sqlerrors where SQ_ADDTIME > current timestamp - 24 hours order by 1 , 2 , 3 ; Note: Union All is used to keep duplicates from all sub- selects. Specifying Union without the “all” keyword will automatically remove duplicates from the final result set.
Insert Statement Syntax: INSERT INTO table-name (column1, column2, ... ) VALUES (value-for-column1, value-for-column2, ... ); INSERT INTO table-name (column1, column2, ... ) Select columna, columnb, … from Table_NameB; Example: INSERT INTO STEVEW_CMS.CMSQRYPRD2 (CQ_CLAIM# ,CQ_ENTNBR,CQ_HICN#,CQ_RCVDTE ,CQ_LNAME ,CQ_FINIT,CQ_CMSDOB,CQ_GENDER,CQ_CMSSSN) Select cast(strip(strip(a.CQ_CLAIM#_,T,' '),B,x'7d') as dec(7,0)) as CQ_CLAIM#_ ,d.enent#,strip(strip(a.CQ_HICN#_,T,' '),B,x'7d') as CQ_HICN#_ ,a.CQ_RCVDTE_ ,a.CQ_LNAME_ ,a.CQ_FINIT_ ,a.CQ_CMSDOB_ ,a.CQ_GENDER_ ,a.CQ_CMSSSN_ FROM DLYFILE.CMSQRYIN A join DLYFILE.CRENTXREF C on cast(strip(strip(a.CQ_CLAIM#_,T,' '),B,x'7d') as dec(7,0))=XRCLM# and c.xrprirole='Claimant' join DLYFILE.CRENTITY D on c.xrent#=d.enent# and upper(A.CQ_LNAME_)=upper(substr(D.ennaml,1,6)) and right(A.cq_cmsssn_,4)=right(D.enssn,4) ;
Insert Statement – Join Explanation Select cast(strip(strip(a.CQ_CLAIM#_,T,' '),B,x'7d') as dec(7,0)) as CQ_CLAIM#_ ,d.enent#,strip(strip(a.CQ_HICN#_,T,' '),B,x'7d') as CQ_HICN#_ ,a.CQ_RCVDTE_ ,a.CQ_LNAME_ ,a.CQ_FINIT_ ,a.CQ_CMSDOB_ ,a.CQ_GENDER_ ,a.CQ_CMSSSN_ FROM STEVEW_CMS.CMSQRYIN1 A join DLYFILE.CRENTXREF C on cast(strip(strip(a. CQ_CLAIM#_ ,T,' '),B,x'7d') as dec(7,0))= XRCLM# - CQ_CLAIM#_ - defined as 30A. - XRCLM# - defined as 7,0 S. - In order to join CMSQRYIN1 to CRENTXREF: - remove trailing blanks, remove x’7d’ from beginning and end, and convert to DEC(7,0).
Insert Statement – Join Explanation Select cast(strip(strip(a.CQ_CLAIM#_,T,' '),B,x'7d') as dec(7,0)) as CQ_CLAIM#_ ,d.enent#,strip(strip(a.CQ_HICN#_,T,' '),B,x'7d') as CQ_HICN#_ ,a.CQ_RCVDTE_ ,a.CQ_LNAME_ ,a.CQ_FINIT_ ,a.CQ_CMSDOB_ ,a.CQ_GENDER_ ,a.CQ_CMSSSN_ FROM STEVEW_CMS.CMSQRYIN1 A join DLYFILE.CRENTXREF C on cast(strip(strip(a.CQ_CLAIM#_,T,' '),B,x'7d') as dec(7,0))=XRCLM# join DLYFILE.CRENTITY D on c.xrent#=d.enent# and upper(A. CQ_LNAME_ )=upper(substr(D. ennaml ,1,6)) and right(A. cq_cmsssn_ ,4)=right(D. enssn ,4) - CQ_LNAME_ - defined as 6A and stored in all lower case. - ENNAML - defined as 30A and stored in mixed case. - CQ_CMSSSN_ - defined as 11A. - ENSSN - defined as 9,0 S. - In order to join to CRENTITY: - convert cq_lname_ and ennaml to upper case, and select only the first 6 bytes from ennaml. - take the 4 right digits from both CQ_CMSSSN_ and ENSSN.
Update Statement Syntax: UPDATE table-name SET column-1 = value-1, column-2 = value-2, ... WHERE search-condition ... UPDATE table-name TBL1 SET column-1 = (Select column-a from table-nameB TBL2 where TBL1.Column-3=TBL2.Column-3) WHERE search-condition ... Example: update STEVEW_CMS.CMSQRYEXP1 a set a.CQ_ADJNAME= ( select strip(b.hefnam,T,' ')||' '||b.helnam from dlyfile.crhoemp b where a.CQ_ADJ#=b.hesubid ) where exists ( select strip(b.hefnam,T,' ')||' '||b.helnam from dlyfile.crhoemp b where a.CQ_ADJ#=b.hesubid ) ;
Delete Statement Syntax: DELETE FROM table-name WHERE search-condition ... Example: delete from mscsql/ciinfi where ciid=708865 and ciinfid in (19189834,19189835,19189836);
Embedded SQL - Dynamic SQL - Allows application to define and run SQL within the program. - Prepared at program run time. - May have more overhead than Static SQL. - Static SQL - Prepared at precompile. - May be more efficient than dynamic. - No Prepare.
Static Embedded SQL – Example exec sql declare c1Count cursor for SELECT count(*) FROM pgmdevlog WHERE pgname = :odobnm and pgcy = :odcy and pgyy = :odyy and pgmm = :odmm and pgdd = :oddd and pgcode = 'IN ' ; exec sql open c1Count; exec sql fetch c1Count into :count; select; when sqlcod = 100; // No SQL entry noLogEntry = *on; when sqlcod = *zeros; // SQL entry found if count > *zeros; noLogEntry = *off; else; noLogEntry = *on; endif; other; // SQL error of some kind noLogEntry = *on; endsl; exec sql close c1count;
Dynamic Embedded SQL – Example @sqlprof = 'SELECT dvsid ' + 'FROM dvs ' + ' inner join pg on pg.pgid = dvs.pgid ' + 'WHERE upper(poltypcde) = ?' + //?PolicyType (CMP) ' and upper(pgdspdsc) = ?' + //?Navigation (GENERAL) ' and upper(dvsdspdsc) = ?' + //?Pagebar (INSURED) 'FOR READ ONLY'; exec sql prepare sprof from :@sqlprof; exec sql declare cprof cursor for sprof; . . exec sql open cprof using :PolicyType, :Navigation, :Pagebar; exec sql fetch cprof into :@dvsid; if sqlcod < 0; exec sql close cprof; WriteSQLERR (‘GetSections’:sqlcod:sqlstate :@sqlprof); return sqlerror; else; . . . exec sql close cprof;
Recommend
More recommend
