Category: SQL

DEFINING EXPRESSION IN QUERY MANAGER

I did not know about the END

EASY_PAY CASE WHEN C.SADPCCSID = 0 THEN ‘N’ ELSE ‘Y’ END

Adding New User to LANSA SQL Database LANSADB

Because we have migrated to a new network profile, our access to our LANSA SQL database will now be denied. These instructions can also be used if inheriting someone’s LANSA install and would like to continue using their existing environment.

You need SQL Management studio to add a new login user to SQL, if not installed you can down load it from Microsoft’s Download center.

http://www.microsoft.com/en-us/download/details.aspx?id=7593

To add a new user to your SQL database

Number 3 Microsoft® SQL Server® 2008 Management Studio Express

Install using your pc name/ SQLSERVER as server name

Right Click LOGINS and select New

This is the ADD screen, use my credentials as a guide.

SQL SERVER 2008

ugly2 — Number 3 Microsoft® SQL Server® 2008 Management Studio Express

Install using your pc name/ SQLSERVER as server name

Right Click LOGINS and select New

This is the ADD screen, use my credentials as a guide.

SQL SERVER 2008

OPTIMIZE SQL RESEARCH ON DBMON OUTPUT

For database monitor analysis queries to run against data collected on IBM i servers with pre-V5R4M0 releases installed.
*) Identify the most time consuming jobs
*) (can use QVC102 instead of QQUSER to group on current user):
SELECT SUM(qqi6) “Total Time”, COUNT(*) “Total SQL Requests”,
qqjnum,qqjob,qquser FROM MAMASON/DBMONSQL
WHERE qqrid=1000 AND qqc21 <> ‘MT’
GROUP BY qqjob,qquser,qqjnum ORDER BY 1 DESC
*) Identify which type of SQL operations account for
the most run time:
SELECT SUM(qqi6) “Total Time”, COUNT(*) “Nbr of Requests”,
qqc21 “Operation Type” FROM MAMASON/DBMONSQL
WHERE qqrid=1000 AND qqc21 <> ‘MT’ AND qqjnum=??’xxxxxx’
GROUP BY qqc21 ORDER BY 1 DESC
*) Which SQL statements account for the most run time:
SELECT SUM(qqi6) “Total Time” , COUNT(*) “Nbr Times Run”, qq1000
FROM MAMASON/DBMONSQL
WHERE qqjnum=??’xxxxxx’ AND qqrid=1000 AND qqucnt<>0
AND qqc21<>’MT’
GROUP BY qq1000 ORDER BY 1 DESC

*) Which SQL statements are the longest running
*) (include Fetch & Close time with SELECT statement):
WITH ExecTime AS (SELECT qqjfld, sum(qqi6)
AS exectot FROM MAMASON/DBMONSQL
WHERE qqrid=1000 AND
(qqc21 IN (‘SI’,’OP’,’FE’,’CL’,’IN’,’UP’,’DL’)
OR (qqucnt>0 AND qqc21 IN(‘SV’,’VI’) )) GROUP BY qqjfld),
StmtText AS (SELECT DISTINCT qqjfld, qq1000L FROM MAMASON/DBMONSQL
WHERE qqrid=1000 AND (qqc21 IN (‘SI’,’OP’,’IN’,’UP’,’DL’)
OR (qqucnt>0 AND qqc21 IN(‘SV’,’VI’) )) )
SELECT sum(x.exectot) “Total Time”, s.qq1000L
FROM ExecTime x, StmtText s
WHERE x.qqjfld = s.qqjfld GROUP BY s.qq1000L ORDER BY 1 DESC

*) Which SQL statements are the longest running and how many
times have they been
*) executed (include Fetch & Close time with SELECT statement):
WITH ExecTime AS (SELECT qqjfld, sum(qqi6) AS exectot FROM
MAMASON/DBMONSQL WHERE qqrid=1000 AND
(qqc21 IN (‘SI’,’OP’,’FE’,’CL’,’IN’,’UP’,’DL’)
OR (qqucnt>0 AND qqc21 IN(‘SV’,’VI’) )) GROUP BY qqjfld) ,
StmtText AS (SELECT DISTINCT qqjfld, qq1000L FROM
MAMASON/DBMONSQL WHERE qqrid=1000 AND (qqc21 IN
(‘SI’,’OP’,’IN’,’UP’,’DL’)
OR (qqucnt>0 AND qqc21 IN(‘SV’,’VI’)))),
StmtCnt AS (SELECT qq1000L, count(*) AS cntr FROM
MAMASON/DBMONSQL WHERE qqrid=1000 AND
(qqc21 IN (‘SI’,’OP’,’FE’,’CL’,’IN’,’UP’,’DL’)
OR (qqucnt>0 AND qqc21 IN(‘SV’,’VI’) ))
GROUP BY qq1000L) SELECT sum(x.exectot)
“Total Time”, max(cntr) “Nbr Times Run”, s.qq1000L
FROM ExecTime x, StmtText s, StmtCnt c
WHERE x.qqjfld = s.qqjfld AND s.qq1000L = c.qq1000L
GROUP BY s.qq1000L ORDER BY 1 DESC

*) Which queries involve table scans and show the
“Estimated rows
*) selected” versus “Total rows in tables”:
WITH tablescans AS (SELECT DISTINCT qqjfld, qqucnt,
qqrest,qqtotr FROM MAMASON/DBMONSQL
WHERE qqrid=3000)
SELECT SUM(qqi6) “Total Time”, COUNT(*) “Times Run”,
a.qqucnt, integer(avg(b.qqrest)) “Est Rows Selected”,
integer(avg(b.qqtotr)) “Total Rows in Table”, qq1000
FROM MAMASON/DBMONSQL a, tablescans b WHERE
qqrid=1000 AND a.qqjfld = b.qqjfld AND qqc21 IN
(‘OP’,’SI’,’SV’,’UP’,’IN’,’DL’)
GROUP BY a.qqucnt, qq1000 ORDER BY 1 DESC

*) Which indexes are advised the most often?
SELECT qqucnt, qvqtbl “Table Name”, qvqlib “Schema”,
qqi2 “Nbr of Primary Keys”, SUBSTR(qqidxd, 1,100) “Keys Advised”
FROM MAMASON/DBMONSQL
WHERE qqrid IN (3000, 3001, 3002) and qqidxa=’Y’ ORDER BY 5,2

*) Which index builds are done the most often?
SELECT qqucnt, qqc16 “Index Reused” qvptbl “Table Name”,
qvplib “Schema”, qqtotr “Rows in Table”,
qqridx “Entries in Index”,
qq1000L “Key Fields” FROM MAMASON/DBMONSQL
WHERE qqrid=3002 AND qqjnum=??’xxxxxx’
ORDER BY qqridx DESC

*) Which queries had access plans rebuilt?
WITH rebuilds AS (SELECT DISTINCT qqjfld, qqucnt, qqrcod
FROM qgpl.snapshot1 WHERE qqrid=3006 )
SELECT a.qqucnt, b.qqrcod “Rebuild Reason”,
qvc24 “Plan Saved Status”, qq1000 FROM qgpl.snapshot1 a,
rebuilds b WHERE a.qqjfld=b.qqjfld AND qqrid=1000
AND qqc21 NOT IN (‘MT’,’FE’,’CL’,’HC’) ORDER BY 4, 1

*) Which queries were processed by SQE vs CQE?
(QQC16=’Y’ implies SQE, ‘N’= CQE)
SELECT qqc16, COUNT(*) FROM MAMASON/DBMONSQL
WHERE qqrid=3014 GROUP BY qqc16

*) Which column statistics have been advised by SQE?
SELECT qqucnt, qvqtbl “Table”, qvqlib “Schema”,
qqc11″Reason Stat Advised”, SUBSTR(qq1000,1,100) “Column name”
FROM MAMASON/DBMONSQL
WHERE qqrid=3015
ORDER BY 2,5

*) Analyze I/O activity for most time consuming SQL
statements: WITH retrieved AS
(SELECT qqjfld, qqi3, qqi5 FROM MAMASON/DBMONSQL
WHERE qqrid=3019 ) SELECT SUM(qqi6) “Total Time” ,
COUNT(*) “Nbr Times Run”,
SUM(b.qqi3) “Sync DB Reads”, SUM((b.qqi5) “ASync DB Reads”,
qq1000 FROM MAMASON/DBMONSQL a, retrieved b
WHERE a.qqjfld=b.qqjfld AND qqrid=1000
AND qqucnt<>0 AND qqc21<>’MT’
GROUP BY qq1000 ORDER BY 1 DESC

*) Which SQL requests are significantly affected by Full Opens:
SELECT SUM(qqi6) “Total Time” , COUNT(*) “Nbr Full Opens”,
qq1000 FROM MAMASON/DBMONSQL
WHERE qqjnum=??’xxxxxx’ AND qqrid=1000 AND qqi5=0
AND (qqc21 IN (‘OP’,’SI’,’DL’,’IN’,’UP’)
OR (qqucnt>0 AND qqc21 IN(‘SV’,’VI’)))
GROUP BY qq1000 ORDER BY 1 DESC

*) What are the reasons causing the Full Opens:
SELECT qqc21, qqc15 “HC Reason”, qqc23 “HC Subcode”,
COUNT(*) “HC Count” FROM MAMASON/DBMONSQL
WHERE qqrid=1000 AND qqc21 IN (‘HC’,’IN’,’UP’,’DL’) AND
qqc15>” AND qqc23>” AND qqjnum= ??’xxxxxx’
GROUP BY qqc21, qqc15, qqc23 ORDER BY 1

*) How long are the stored procedure calls running and how many
*) SQL operations are being performed during the calls
(Replace QQC103 with QVC1282 to return the “long”
SQL Procedure Name)?
SELECT qqc104 AS “Proc Schema”, qqc103 AS “Procedure” ,
SUM (qqi6) “Total Time” , COUNT (*) “Nbr of Requests” ,
qqc21 “Operation Type” FROM MAMASON/DBMONSQL
WHERE qqrid=1000 AND qqc21<>’MT’ AND qqc103<> ‘ ‘
GROUP BY qqc104, qqc103, qqc21 ORDER BY 1,2,5

*) Which SQL instances (QQUCNT) account for the most run time ?
SELECT SUM(qqi6) “Total Time” , COUNT(*) “Nbr Times Run”, qqucnt
FROM MAMASON/DBMONSQL
WHERE qqjnum=??’xxxxxx’ AND qqrid=1000 AND qqucnt<>0
AND qqc21<>’MT’
GROUP BY qqucnt ORDER BY 1 DESC

*) Which SQL instances (QQUCNT & text) account for
the most run time ?
SELECT SUM(qqi6) “Total Time” , COUNT(*) “Nbr Times Run”,
qqucnt, qq1000
FROM MAMASON/DBMONSQL
WHERE qqjnum=??’xxxxxx’ AND qqrid=1000 AND qqucnt<>0
AND qqc21<>’MT’
GROUP BY qqucnt, qq1000 ORDER BY qqucnt, 1 DESC

*) Which queries involve use of a query sort?
WITH sorts AS (SELECT qqjfld, qqucnt FROM MAMASON/DBMONSQL
WHERE qqrid=3003 )
SELECT SUM(qqi6) “Total Time” , COUNT(*) “Nbr Times Run”,
a.qqucnt, qq1000
FROM MAMASON/DBMONSQL a, sorts b
WHERE qqrid=1000 AND a.qqjfld=b.qqjfld
GROUP BY a.qqucnt,qq1000 ORDER BY 1 DESC

*) Analyze I/O & CPU activity for most frequently
run SQL statements:
WITH listSQL AS (SELECT DISTINCT qqjfld, qq1000L
FROM MAMASON/DBMONSQL WHERE qqrid=1000 AND
(qqc21 IN (‘SI’,’OP’,’IN’,’UP’,’DL’)
OR (qqucnt>0 AND qqc21 IN(‘SV’,’VI’) )) )
SELECT COUNT(*) timesrun, SUM(a.qqi1) cpums,
SUM(a.qqi2) elpms, SUM(a.qqi3) synrds,
SUM(a.qqi4) synwrts, SUM(a.qqi5) asyrds,
SUM(a.qqi6) asywrts, SUM(a.qqi7) rowsread,
SUM(a.qqi8) as readcalls,
qq1000L FROM MAMASON/DBMONSQL a, listSQL b
WHERE a.qqjfld=b.qqjfld AND a.qqrid=3019
GROUP BY b.qq1000L ORDER BY 1 DESC

*) Which non-SQL queries are the most time consuming?
WITH retrieved AS (SELECT qqjfld, qqucnt, qqi2,qqi7
FROM MAMASON/DBMONSQL WHERE qqrid=3019 )
SELECT (qqi1+b.qqi2) “Total Query Time”,
b.qqi7 “Number Rows Retrieved”, qqc101 “Open ID”, qquser
FROM MAMASON/DBMONSQL a, retrieved b
WHERE a.qqjfld=b.qqjfld AND qqrid=3014
ORDER BY 1 DESC

SQL AND QUERY400 OPTIMINIZATION

•STRDBMON OUTFILE(MAMASON/DBMONSQL) JOB(*ALL)

TYPE(*DETAIL) FTRUSER(MAMASON)

Turns on Database Monitoring and directs output to a source file.

File DBMONSQL will be created if not exists.

•While Monitor is running all Query Optimization details are captured in source.

•Best results for system optimization suggest a few weeks of monitoring data.

•Output MAMASON/DBMONSQL can be queried

•Access Path Creation is suggested when field QQIDXA = Y in DBMONSQL file

•Logicals Used is in field is shown in QQIFNM

•Fields to add to logical keys are shown in QVC3003

•SELECT QQPTLN, QQPTFN, SUM(QQTOTR), QQIDXD FROM DBQRYLOG/DBMON WHERE QQIDXA=’Y’ GROUP BY QQPTLN, QQPTFN, QQIDXD ORDER BY QQPTLN,QQPTFN,QQIDXD

•Shows which access paths are used most in the system, see if a new logical makes sense

•Sometimes adding additional keys to existing logicals makes sense.

•Monitor will stay in force till End Request issued.

• ENDDBMON JOB(*ALL)

SAP Comments and Good Sql Example using Case

Open AP Invoices (Header)

SELECT DISTINCT

APMIHP.SPID,

APMIHP.POID AS POID_1,

APMIHP.INVID AS INVID_1,

SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 7, 2 ) || ‘/’ || SUBSTRING( CAST(

APMIHP.IHINVD AS CHAR( 8 ) ), 5, 2 ) || ‘/’ || SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ),

1, 4 ) AS COLUMN0000,

APMIHP.ATID,

CASE

WHEN ( APMIHP.CMPNBR = ‘301’ AND APMIHP.CURCDE <> ‘USD’ )

THEN SUM ( DGDAMC ) * – 1

ELSE 0

END AS COLUMN0003,

CASE

WHEN ( APMIHP.CMPNBR = ‘301’ AND APMIHP.CURCDE <> ‘USD’ )

THEN APMIHP.CURCDE

ELSE ‘ ‘

END AS COLUMN0004,

CASE

WHEN ( APMIHP.CMPNBR = ‘301’ AND APMIHP.CURCDE = ‘USD’ )

THEN SUM ( DGDAMC ) * – 1

ELSE 0

END AS DGDAMC_1,

CASE

WHEN ( APMIHP.IHHOLD = ‘1’ ) THEN APMIHP.IHHOLD

ELSE ‘ ‘

END AS IHHOLD,

‘ ‘ AS COLUMN0005,

‘ ‘ AS COLUMN0006,

CASE

WHEN ( APMIHP.AYID = ‘CHECK’ ) THEN ‘C’

WHEN ( APMIHP.AYID = ‘ACH’ ) THEN ‘U’

WHEN ( APMIHP.AYID = ‘WIRE’ ) THEN ‘1’

ELSE APMIHP.AYID

END AS AYID,

APMIHP.CMPNBR

FROM

CHICAGO.EPDB.APMIHP APMIHP,

CHICAGO.EPDB.APMDGP APMDGP

WHERE

APMIHP.CMPNBR = APMDGP.CMPNBR

AND APMIHP.PFTCTR = APMDGP.PFTCTR

AND APMIHP.SPID = APMDGP.SPID

AND APMIHP.INVID = APMDGP.INVID

AND APMIHP.AYID = APMDGP.AYID

AND ( ( APMIHP.IHSTAT = ‘1’)

AND ( APMIHP.IHCAMT APMIHP.IHPAMT <> 0)

AND ( APMIHP.CMPNBR = 301)

AND ( NOT ( ( SUBSTRING( APMIHP.INVID, 1, 5 ) = ‘RECUR’)

AND ( APMIHP.IHINVD > 20110901)))

AND ( APMDGP.GLACCT = 10100))

Open AP Invoices (Comments)

SELECT

APMIHP.SPID,

APMIHP.POID AS POID_1,

APMIHP.INVID AS INVID_1,

SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 7, 2 ) || ‘/’ ||

SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 5, 2 ) || ‘/’ ||

SUBSTRING( CAST( APMIHP.IHINVD AS CHAR( 8 ) ), 1, 4 ) AS COLUMN0000,

AYFCKCM.CHECK_COMMENT,

APMIHP.CMPNBR

FROM

CHICAGO.EPDB.APMIHP APMIHP,

CHICAGO.EPDB.AYFCKCM AYFCKCM

WHERE

APMIHP.CMPNBR = AYFCKCM.CMPNBR

AND APMIHP.SPID = AYFCKCM.SPID

AND APMIHP.PFTCTR = AYFCKCM.PFTCTR

AND APMIHP.INVID = AYFCKCM.INVID

AND ( ( APMIHP.IHSTAT = ‘1’)

AND ( APMIHP.IHCAMT APMIHP.IHPAMT <> 0)

AND ( APMIHP.CMPNBR = 301)

AND ( NOT ( ( SUBSTRING( APMIHP.INVID, 1, 5 ) = ‘RECUR’)

AND ( IHINVD > 20110901))))

Note:

SPID is the current AS400 vendor ID

ATID is the AS400 payment term ID this should be converted to SAP payment term ID

The IHINVD is the invoice date select anything from the current run date

The clause (APMIHP.IHCAMT APMIHP.IHPAMT <> 0) stands for Open item with pending $

The clause ( NOT ( ( SUBSTRING( APMIHP.INVID, 1, 5 ) = ‘RECUR’) means we exclude all the future recurring invoices

iseries SQL Find missing child or parent with NOT EXISTS

Example

SELECT sdfcabn.stcuno FROM sdfcabn WHERE not exists (select * from

cufship where cufship.stcuno = sdfcabn.stcuno)

Delete Duplicate Records in File Before you Assign Keys using SQL

Let’s say you are building a file from multiple spreadsheets and need to consolidate and remove duplicates. Build your DB2 file without keys load all the spread sheets with *add not *replace then run this SQL using the where statement to match the fields

Delete from ksfcexg F1 where RRN(f1) > (select MIN(RRN(F2)) from

ksfcexg F2 where F2.stcuno = F1.stcuno)

F1 and F2 are the same file

So frackin hard

CREATE TABLE EMPLOYEE3 AS
   (SELECT PROJNO, PROJNAME, DEPTNO
    FROM EMPLOYEE
    WHERE DEPTNO = 'D11') WITH NO DATA

MUST USE "WITH NO DATA"  at the end of statement - bullshit fibm

SELECT_SQL Examples

SELECT_SQL FIELDS((#W_COUNT ‘count(*)’)) FROM_FILES((REQUEST )) WH
           ERE(‘CLIENT_ID = :RITAID AND STATUS_CODE = 4    AND BA
           TCH_SEQ_NUM = 0’)
ENDSELECT
SELECT_SQL FIELDS((#CRGUID ) (#CRTED ) (#CROBATCH ) (#CRBATCH ) (#
           CRCCNO ) (#CREXPDT ) (#CRAMNT ) (#CRSWIPE ) (#CRQOVR )
           (#CRRITAS ) (#CRRITAID ) (#CRTCN ) (#CRMUSR ) (#CRCCET
           ) (#CRORIG ) (#CRFAIR )) FROM_FILES((CRTRAN )) WHERE(‘S
           UBSTR(CROBATCH,1,2) = :XBFRGN AND CRSWIPE <> :WSWIPE AN
           D (CRRITAS = 5 OR (CRRITAS = 200 AND CRAMNT < 0))’) ORD
           ER_BY(‘CROBATCH,CRBATCH’)
********** OK to Mark for Completion
SELECT_SQL FIELDS((#W_COUNT ‘count(*)’) (#W_SUM ‘SUM(INT(TRANS_AMO
           UNT))’)) FROM_FILES((REQUEST )) WHERE(‘CLIENT_ID = :RIT
           AID AND STATUS_CODE = 4    AND BATCH_SEQ_NUM = 0 AND C
           OMMAND =:COMMND’) IO_STATUS(#W_IOSTS) IO_ERROR(*NEXT)
ENDSELECT
********** put decimal place in amount

SUBROUTINE NAME(GETRITASTS)
**********
SELECT_SQL FIELDS((#INTRN0001 ‘INTRN_SEQ_NUM’) (#STATU0001 STATU00
           001)) FROM_FILES((REQUEST )) WHERE(‘ORIG_SEQ_NUM = :WRT
           AID’) ORDER_BY(STATU00001) IO_ERROR(*NEXT)
LEAVE
ENDSELECT
**********
IF_NULL    FIELD(#INTRN0001)
SELECT_SQL FIELDS((#INTRN0001 ‘INTRN_SEQ_NUM’) (#STATU0001 STATU00
           001)) FROM_FILES((REQUEST )) WHERE(‘INTRN_SEQ_NUM= :WRT
           AID’) ORDER_BY(STATU00001) IO_ERROR(*NEXT)
LEAVE
ENDSELECT

SELECT_SQL FIELDS((#W_COUNT ‘count(*)’)) FROM_FILES((REQUEST )) WH
           ERE(‘CLIENT_ID = :WRITAI AND STATUS_CODE = :WRITAS AND
             BATCH_SEQ_NUM = 0’)
ENDSELECT

iseries SQL Help Functions and Commands

Iseries / AS400 SQL Built-In Functions

Basic Functions

Function Description
===============================

MAX         Maximum value from a set of pre-defined values.
MIN         Minimum value from a set of pre-defined values.
AVG         Average value of a set of pre-defined values.
SUM         Sum of a set of pre-defined values.
COUNT         Number of elements in a set of pre-defined values.

Numeric Functions

Function Description
=======================================

ABS(N)             Absolute value of N.
COS(A) / ACOS(A)
SIN(A) / ASIN(A)
TAN(A) / ATAN(A)     Basic trigonometric functions.
CEILING(N)
FLOOR(N)         Rounding of N to the unit
above/below.
DEGREES(R)         Converts a value in radians to degrees.
RADIANS(D)         Converts a value in degrees to radians.
LN(N)
LOG10(N)         Natural logarithm / base 10 logarithm of N.

String Functions

Function Description
====================================

CHAR(N)         The string representation of the number N.
CHAR_LENGTH(S)         Length of a string.
CONCAT(S1, S2)         Concatenates S1 with S2.
SUBSTR(S, I, L)     Returns a substring of S, starting at index I of lenght L.
LOWER(S)         Lowercase representation of S.
UPPER(S)         Uppercase representation of S.
TRIM(S)         Removes spaces from the beggining and and of S.
RTRIM(S)        Removes spaces at the begging (right) of S.
LTRIM(S)         Removes spaces at the begging (left) of S.

Date and Time Functions

Function     Description
============================
CURDATE()    System’s current date.
CURTIME()     System’s current time.
DATE(D)     Converts a string representation of a date into into a date value.
DATE(T)     Converts a string representation of a time into into a time value.
DAY(D)         Day(1-31) from the date D.
WEEK(D)     Week (1-54) from the date D.
MONTH(D)     Month (1-12) from the date D.
YEAR(D)     Year from the date D.
DAYOFWEEK(D)     Day (1-7) from the date D where 1 is Sunday.
DAYOFWEEK_ISO(D)Day (1-7) from the date D where 1 is Monday.
DAYOFYEAR(D)     Number of the day, in a year (1-366).
HOUR(T)     Hour (0-24) from the time T.
MINUTE(T)     Minute from the time T.
SECOND(T)     Second from the time T.
MICROSECOND(T)     Microsecond from the time