Difference between revisions of "SQL"

From MidrangeWiki
Jump to: navigation, search
(+dialect explanation, fix link to Kevin's article, QMQRY, QAQQINI)
(Put Sub headings on SQL Tips and Add a Tip from Scott Klement)
Line 35: Line 35:
  
 
==Tips==
 
==Tips==
;SQL7008 error
+
===SQL7008 error===
 
:{{code|&FILE}} in {{code|&LIBRARY}} not valid for operation.
 
:{{code|&FILE}} in {{code|&LIBRARY}} not valid for operation.
 
:-- Code 3 -- {{code|&FILE}} is not journaled, or you do not have authority to the journal.  Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal.  
 
:-- Code 3 -- {{code|&FILE}} is not journaled, or you do not have authority to the journal.  Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal.  
 
*Resolution: Use {{code|[[RUNSQLSTM]]}} with parameter {{code|COMMIT(*NONE)}}
 
*Resolution: Use {{code|[[RUNSQLSTM]]}} with parameter {{code|COMMIT(*NONE)}}
  
;SELECT *
+
===SELECT * ===
 
:For the sake of example, use two files, {{code|master}} and {{code|trans}}.  We want to select all the columns in {{code|trans}} but only the name column from table {{code|master}}.
 
:For the sake of example, use two files, {{code|master}} and {{code|trans}}.  We want to select all the columns in {{code|trans}} but only the name column from table {{code|master}}.
 
:If using *SYS naming you can execute {{code|SELECT trans.*, name FROM trans JOIN master ON...}}  
 
:If using *SYS naming you can execute {{code|SELECT trans.*, name FROM trans JOIN master ON...}}  
 
:If using *SQL naming, we need to use a correlation name: {{code|SELECT t.*, name FROM trans t JOIN master ON...}}
 
:If using *SQL naming, we need to use a correlation name: {{code|SELECT t.*, name FROM trans t JOIN master ON...}}
  
;RUNSQL command for ad-hoc SQL statements
+
===RUNSQL command for ad-hoc SQL statements===
 
:[http://www.mcpressonline.com/tips-techniques/sql/partner-techtip-blend-sql-and-rpgle-to-make-better-tools.html "Partner TechTip: Blend SQL and RPGLE to Make Better Tools"] by [http://www.mcpressonline.com/product-reviews/?func=fbprofile&task=showprf&userid=65946 Kevin Forsythe]
 
:[http://www.mcpressonline.com/tips-techniques/sql/partner-techtip-blend-sql-and-rpgle-to-make-better-tools.html "Partner TechTip: Blend SQL and RPGLE to Make Better Tools"] by [http://www.mcpressonline.com/product-reviews/?func=fbprofile&task=showprf&userid=65946 Kevin Forsythe]
  
 +
===SQL via QM Query===
 
Another method is based on Query Management Query.  Create the QMQRY as all substitution variables and populate them in CL.  [http://faq.midrange.com/data/cache/322.html Midrange FAQ] entry for the unfortunately named RUNSQLSTM.
 
Another method is based on Query Management Query.  Create the QMQRY as all substitution variables and populate them in CL.  [http://faq.midrange.com/data/cache/322.html Midrange FAQ] entry for the unfortunately named RUNSQLSTM.
  
;Query Options File
+
===SQL via QSH===
 +
original post [[http://archive.midrange.com/midrange-l/200807/msg01362.html]]                   
 +
Use the db2 command in QShell as a really easy way to implement this sort of thing.
 +
 
 +
Write a simple QShell script like this:
 +
 
 +
[code]
 +
#!/usr/bin/qsh
 +
LIB=$1
 +
TABLE=$2
 +
COL=$3
 +
db2 "select $COL,count(*) from $LIB.$TABLE \
 +
group by $COL, order by $COL" \
 +
> /tmp/report.txt
 +
[/code]
 +
 
 +
Run that Qshell script (or submit it to batch, if you like)
 +
 
 +
SBMJOB CMD(QSH CMD('myscript.sh MYLIB MYTABLE MYCOL'))
 +
 
 +
 
 +
===Query Options File===
 
:IBM i allows one to alter the behaviour of the Query optimiser by use of a file called [http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/qryoptf.htm QAQQINI]  IBM supply a template in QSYS which you can copy into QUSRSYS and alter to suit your needs.  If you want a custom options file, copy it into your own library and use the command [[CHGQRYA]] to tell the system where to find it.  The system uses the one in QUSRSYS as a system-wide default.
 
:IBM i allows one to alter the behaviour of the Query optimiser by use of a file called [http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/qryoptf.htm QAQQINI]  IBM supply a template in QSYS which you can copy into QUSRSYS and alter to suit your needs.  If you want a custom options file, copy it into your own library and use the command [[CHGQRYA]] to tell the system where to find it.  The system uses the one in QUSRSYS as a system-wide default.
  
 +
 +
===SQL Settings===
 
On V5R4, using [[OLAP]] can mean having a good look at the value of {{code|IGNORE_DERIVED_INDEX}}.  One may want to set it to {{code|YES}} in order to use a heritage database with OLAP.
 
On V5R4, using [[OLAP]] can mean having a good look at the value of {{code|IGNORE_DERIVED_INDEX}}.  One may want to set it to {{code|YES}} in order to use a heritage database with OLAP.
  

Revision as of 19:24, 30 July 2008

SQL, or Structured Query Language, is a platform independent way of accessing databases. However, the version of SQL which runs on the AS/400, known as SQL400 or SQLDB2, does have syntax differences from other SQL dialects such as Microsoft SQL Server or Oracle, as well as DB2 on other platforms. Detractors of DB2 for i tend to point up these differences as deficiencies, but every vendor has differences that make each dialect somewhat incompatible.

Terminology

SQL Term iSeries Term
TABLE PHYSICAL FILE
ROW RECORD
COLUMN FIELD
INDEX KEYED LOGICAL FILE
ACCESS PATH
VIEW NON-KEYED LOGICAL FILE
SCHEMA LIBRARY
COLLECTION
LOG JOURNAL
ISOLATION LEVEL COMMITMENT CONTROL LEVEL

[1]

Tips

SQL7008 error

&FILE in &LIBRARY not valid for operation.
-- Code 3 -- &FILE is not journaled, or you do not have authority to the journal. Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal.
  • Resolution: Use RUNSQLSTM with parameter COMMIT(*NONE)

SELECT *

For the sake of example, use two files, master and trans. We want to select all the columns in trans but only the name column from table master.
If using *SYS naming you can execute SELECT trans.*, name FROM trans JOIN master ON...
If using *SQL naming, we need to use a correlation name: SELECT t.*, name FROM trans t JOIN master ON...

RUNSQL command for ad-hoc SQL statements

"Partner TechTip: Blend SQL and RPGLE to Make Better Tools" by Kevin Forsythe

SQL via QM Query

Another method is based on Query Management Query. Create the QMQRY as all substitution variables and populate them in CL. Midrange FAQ entry for the unfortunately named RUNSQLSTM.

SQL via QSH

original post [[1]]                     

Use the db2 command in QShell as a really easy way to implement this sort of thing.

Write a simple QShell script like this:

[code]

  1. !/usr/bin/qsh

LIB=$1 TABLE=$2 COL=$3 db2 "select $COL,count(*) from $LIB.$TABLE \ group by $COL, order by $COL" \ > /tmp/report.txt [/code]

Run that Qshell script (or submit it to batch, if you like)

SBMJOB CMD(QSH CMD('myscript.sh MYLIB MYTABLE MYCOL'))


Query Options File

IBM i allows one to alter the behaviour of the Query optimiser by use of a file called QAQQINI IBM supply a template in QSYS which you can copy into QUSRSYS and alter to suit your needs. If you want a custom options file, copy it into your own library and use the command CHGQRYA to tell the system where to find it. The system uses the one in QUSRSYS as a system-wide default.


SQL Settings

On V5R4, using OLAP can mean having a good look at the value of IGNORE_DERIVED_INDEX. One may want to set it to YES in order to use a heritage database with OLAP.

References

  1. Business Intelligence Indexing and statistics strategies for DB2 UDB for iSeries by Michael W. Cain iSeries Teraplex Integration Center, December 2003, Version 3.0, Template:PDF

See also

See also: SQL Primer and SOUNDEX
See also on Wikipedia: SQL

Categories