DB2 and SQL Terminology DB2 : Relational database manager fully - - PowerPoint PPT Presentation

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 journal, a journal receiver, an SQL catalog, and an optional data dictionary. A schema enables the user to find the objects by name. Another name for a schema is collection.

• Library. A group of related objects that enables the user

to find the objects by name.

• Table. A set of columns and rows.

Physical file. A set of records.

• Row. The horizontal part of a table containing a serial

set of columns.

• Record. A set of fields.
• 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

tables. Logical file. A subset of fields or records of up to 32 physical files.

• Index. A collection of data in the columns of a table,

logically arranged in ascending or descending order.

• Index. A type of logical file.
• Package. An object that contains control structures for

SQL statements to be used by an application server. SQL package. An object that contains control structures for SQL statements to be used by an application server.

• Catalog. A set of tables and views that contain

information about tables, packages, views, indexes, and constraints. No similar object. However, the Display File Description (DSPFD) and Display File Field Description (DSPFFD) 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

• rder by 1;
• Results:
• 100 Claim
• 80 MedProvidr
• 60 Attorney
• 50 Adjuster
• 40 Misc
• 20 Draft

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

• rder 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

• rder 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;

• ther; // 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;

Embedded SQL – SQL Error Handling

• Previous Method
• Hundreds of programs – Each Program writes to file when SQLCODE < 0
• Need For SQLSTATE

SQLState Description SQL Code

• Updated Method
• Service Program written, and called, when SQL Error occurs.
• SQLState Added to new file.

22001 Character data, right truncation occurred; for example, an update or insert value

• 302, -303, -404,

is a string that is too long for the column, or a datetime value cannot be assigned

• 433, -802

to a variable, because it is too small. 22003 A numeric value is out of range.

• 302, -304, -406,
• 446, -802

22023 A parameter or variable value is invalid.

• 302, -304, -406,
• 802

• DB2 Catalog

– DB2 “metadata” – provides information about the DB2 environment.

• SYSTABLES

– Information for every table, alias, or view in SQL schema.

• SYSTABLESTAT

– Statistical information for every table in SQL schema.

• SYSINDEXES

– Information on every index in SQL schema.

• SYSKEYS

– Information on every column of an SQL Index

• Link to all DB2 catalog information:

– http://publib.boulder.ibm.com/infocenter/IBMi/v6r1m0/topic/db2/rbafzcatalogtbls.htm?resultof=%22%73%79%73%74%61% 62%6c%65%73%22%20%22%73%79%73%74%61%62%6c%22%20

• Practical Example:

– Goal: Need to quickly determine if any of the AC2 history tables have any rows at all where the effective timestamp column is not null. – Approach: » select 'select count(*) from '||strip(system_table_schema)||'.'||table_name||' where effective_timestamp is not null;' from qsys2.systables where system_table_schema='PRDSQL' and table_name like '%_HIST';

• System i Navigator:

– Manage DB2 Objects. – Monitor Active Jobs. – Peruse Output Queues. – Access Files On The Integrated File System. – Backup The System. – Attend To Authorization Lists. – Assess And Monitor DB2 Performance.

• Key feature – Other versions of DB2 don’t have this and organizations purchase outside

vendor tools.

– Run SQL Scripts And Save Them For Future Usage. – And, probably much more!

• IBMi Navigator Performance Related Features (not an all-

inclusive list!)

– Index Advisor:

• Determines if creation of permanent index will help query performance.
• Condensed Index Advisor
• Can also be accessed from the Visual Explain interface (will cover briefly later).

– SQL Plan Cache:

• Information about SQE queries in the database.
• Actively changes and plans can be removed from the cache.
• Plan Cache Snapshot can be taken – information contained in files.

– Visual Explain:

• Graphical representation of the access paths DB2 will use to satisfy the query.
• Select, Insert, Update, Delete all supported.
• Ability to highlight expensive operations.
• Ability to invoke Statistics or Index Advisor from Visual Explain.

Index Advisor:

SQL Plan Cache:

• Monitor Plan Cache size and summary SQL activity.

SQL Plan Cache:

SQL Plan Cache:

SQL Plan Cache Notes:

• Plan Cache Automatically Flushed After IPL.
• Plan Cache Size
• Dynamically adjusted from properties window.
• Ramifications of making the plan cache too large?
• After IPL - adjusted to 512 mb default
• PTF - MF52486
• Application of PTF will preserve size, after adjusting the size once more
• Call qsys2.change_plan_cache_size(3072);

SQL Plan Cache Snapshots:

SQL Plan Cache Snapshots

SQL Plan Cache Snapshots

SQL Plan Cache Snapshots (Table Scan Summary)

Program To Create Plan Cache Snapshot

h INDENT('| ') OPTION(*NODEBUGIO:*SRCSTMT) d CURRENTDATE S D d SNAPSHOTNAME S 10A /FREE CURRENTDATE = %DATE(); SNAPSHOTNAME = 'PC' + %CHAR ( %SUBDT(CURRENTDATE:*YEARS)) + %CHAR ( %SUBDT(CURRENTDATE:*MONTHS)) + %CHAR ( %SUBDT(CURRENTDATE:*DAYS)); EXEC SQL CALL QSYS2/DUMP_PLAN_CACHE('SWALLACE', :SNAPSHOTNAME); // DID AN ERROR OCCUR? IF %SUBST(SQLSTATE:1:2)<> '00'; // DO SOMETHING...LIKE SEND A MESSAGE TO QSYSOPR ENDIF; RETURN; /END-FREE Note: Program runs through the IBMi Job Scheduler every Friday night at 23:45

Visual Explain:

Visual Explain:

Visual Explain:

Visual Explain:

Visual Explain:

Visual Explain:

Visual Explain:

Visual Explain:

Visual Explain:

The End