never ending pasta pass

September 24th, 2014

http://neverendingpastapass.com do you have one of these passes?

SQL – INNER vs Left

August 28th, 2014

Select sah.*,ari.*
From prddtalib.slsah sah
Inner join prddtalib.arari ari on ari.UCIHACTID = sah.actid
left join prddtalib.slsad sad on sad.actid=ari.UCITACTID
Where sad.sahnum is null

vs

SELECT * FROM devdtalib/slsah, devdtalib/arari WHERE SLSAH.ACTID =
ARARI.UCIHACTID and NOT EXISTs( SELECT SLSAD.ACTID FROM
DEVDTALIB/SLSAD where ARARI.UCITACTID = SLSAD.ACTID)

UBER COUPON CODES

June 19th, 2014

http://uber-code.com/

UBER COUPON

June 5th, 2014

Free 20$ Über ride. Use this code t3o4m #uberlove

t3o4m

t3o4m

t3o4m

LANSA Template Programming

February 13th, 2014

Here is some template variables to use

@@CLR_LST
Clear a work list

@@CMP_IDX
Compare an index value

@@COMMENT
Add a comment line to the generated RDML code

@@DEC_IDX
Decrement an index value

@@GET_FILS
Get the files to be used in this template

@@GOTO
Pass control to a label

@@IF
Test a condition and pass control to a label

@@INC_IDX
Increment an index value

@@LABEL
A label which is the subject of another command

@@MAK_LSTS
Make a list of selected fields from another list(s)

@@MRG_LSTS
Merge a list(s) with another list(s)

@@QUESTION
Ask a question and receive a reply

@@RTV_FLDS
Retrieve the fields in a file

@@RTV_KEYS
Retrieve the keys of a file

@@RTV_RELN
Retrieve the relationship between two files

@@SET_IDX
Set an index value

FILES:

@@FNAMEnn
File name
A
10

@@FLIBRnn
File library
A
10

@@FVERSnn
File version number
N
15
5

@@FTYPEnn
File type (P=PF, L=LF)
A
1

@@FDESCnn
File description
A
40

@@FBASPnn
Based in physical file name
A
10

@@FRELFnn
Related file name
A
10

@@FRELLnn
Related file library
A
10

@@FRELVnn
Related file version number
N
15
5

@@FRELRnn
Related file relationship (O=1:1, M=1:n)
A
1

@@FRELAnn
Related file access route name
A
10

@@FRELCnn
Related file connection type. The connection types are:

BASE = Base file
DIRBASE = Directly connected to the base file
INDBASE = Indirectly connected to the base file
DETAIL = Detail file
(i.e. 1 : Many related file)
DIRDETL = Directly connected to detail file
INDDETL = Indirectly connected to detail file
A
70

@@FAREAnn
Header or browse area (H=HDR,B=BRW)
A
1

@@TFMX
Maximum file number selected
N
15
5

@@TFMN
Minimum file number selected
N
15
5

FIELDS:

Variable
Description
Type
Len
Dec

1
@@LSTnn
List name – all elements of list
A
Variable

1
@@LSUnn
List name – all elements of list
A
Variable

2
@@LSXnn/yy
List name – first yy elements of list
A
Variable

1
@@LNEnn
Number of elements in list nn
N
2
0

3
@@LELnnxx
Element xx of list nn
A
10

3
@@LATnnxx
Attributes of element xx of list nn
A(7)
10

3
@@LDSnnxx
Description of element xx of list nn
A
40

3
@@LTPnnxx
Type of element xx of list nn (A,P,S)
A
40

Adding New User to LANSA SQL Database LANSADB

January 6th, 2014

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

SQL SERVER 2008

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

Install using your pc name/ SQLSERVER as server name
ugly3

Right Click LOGINS and select New
ugly4

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

ugly6

ugly7

ugly8

SQL SERVER 2008

 

 

How to use GMAIL POP for Wireless camera

December 25th, 2013

Address: pop.gmail.com

port: 465

gmail address

gmail password

SSL must be yes or checked.

 

For yahoo you must upgrade to plus.

Everything you wanted to know about LANSA Propagation but was afraid to ask

December 24th, 2013

LANSA propagation is for the Visual LANSA environment so the IDE’s on everyone’s PC will have the new objects that your co-workers have created or modified and have been promoted.

Using “LANSA PCMAINT” you can see the PC’s that are being used for LANSA’s VL IDE, some may be very old and can be removed.

There is an option to use WorkGroups, which are for smaller teams that need to be in sysnc with each other but not all the developers.  I can’t think of a reason to use this, so you should be able to leave this blank.

Now Repository Groups are so that any PC in this group will be propagated too.  This is where you add PC Nodes for Propagation.

So you have added all your PC’s to the Repository Group and created a new field on your IDE and checked it in, AND PROPAGATION STILL DOES NOT WORK?

 

Think of Propagation assignments like creating a TASK on the iseries, in order for your PC to get Propagated your need to do a SYSTEM INITIALIZATION.

Makes sense, would have been nice to see this in the Docs.

 

Another way to see who on your team is ignoring you and still has not initialized their IDE, you can look at DC@FPP in the interal LANSA Data base.

 

EXAMPLE:

select * from dc@fpp

BOSSERB01      DEV                 002001ZZZMAM

 

 

See you at the Next LANSA Conference.

 

Mark

 

 

 

 

 

LANSA WEBSERVER IS NOT LICENSED

December 18th, 2013

For a model b configuration with a PC server, I recently found out that when you run the X_CPU.exe utility, the selection of a NIC card to use to generate the CPU text file will point your LANSA configuration to that NIC card.

Normally I go with the first NIC card I see when no information is provided, upon hearing that CPU not found in database I thought maybe try the 2nd nic card.

This generates a LANSA WEBSERVER IS NOT LICENSED error page.

Now you know.

 

OPTIMIZE SQL RESEARCH ON DBMON OUTPUT

November 21st, 2013

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