Difference between revisions of "SQL"
From MidrangeWiki
Starbuck5250 (talk | contribs) m (→Terminology: typo) |
Starbuck5250 (talk | contribs) (+dialect explanation, fix link to Kevin's article, QMQRY, QAQQINI) |
||
| Line 1: | Line 1: | ||
| − | {{AN}}, or {{bu|S|tructured}} {{bu|Q|uery}} {{bu|L|anguage}}, 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 | + | {{AN}}, or {{bu|S|tructured}} {{bu|Q|uery}} {{bu|L|anguage}}, 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== | ==Terminology== | ||
| Line 46: | Line 46: | ||
;RUNSQL command for ad-hoc SQL statements | ;RUNSQL command for ad-hoc SQL statements | ||
| − | :[http://www.mcpressonline.com/ | + | :[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] |
| + | |||
| + | 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 | ||
| + | :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. | ||
| + | |||
| + | 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. | ||
| + | |||
| + | * [http://www.itjungle.com/mgo/mgo091203-story01.html Adjust Default Query Optimizer Settings with QAQQINI] Midrange Guru, Sept 2003. | ||
| + | * [http://www.mcpressonline.com/programming/general/my-own-private-qaqqini.html My Own Private QAQQINI] MC Press Online, May 2001 | ||
==References== | ==References== | ||
Revision as of 14:08, 29 April 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 |
Tips
- SQL7008 error
&FILEin&LIBRARYnot valid for operation.- -- Code 3 --
&FILEis 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
RUNSQLSTMwith parameterCOMMIT(*NONE)
- SELECT *
- For the sake of example, use two files,
masterandtrans. We want to select all the columns intransbut only the name column from tablemaster. - 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
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.
- 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.
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.
- Adjust Default Query Optimizer Settings with QAQQINI Midrange Guru, Sept 2003.
- My Own Private QAQQINI MC Press Online, May 2001
References
- ↑ 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
