Difference between revisions of "SQL"
From MidrangeWiki
Starbuck5250 (talk | contribs) (+ select *) |
MrDolomite (talk | contribs) (add terminology xref) |
||
| 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 standard | + | {{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 standard SQL. |
| − | { | + | ==Terminology== |
| + | {|style="position:relative; margin: 0 0 0.5em 1em; border-collapse: collapse; background:white; clear:right; text-align:center;" border="1" cellpadding="7" | ||
| + | !SQL Term | ||
| + | !iSeries Term | ||
| + | |- | ||
| + | |TABLW | ||
| + | |PHYSICAL FILE | ||
| + | |- | ||
| + | |ROW | ||
| + | |RECORD | ||
| + | |- | ||
| + | |COLUMN | ||
| + | |FIELD | ||
| + | |- | ||
| + | |INDEX | ||
| + | |KEYED LOGICAL FILE<br>ACCESS PATH | ||
| + | |- | ||
| + | |VIEW | ||
| + | |NON-KEYED LOGICAL FILE | ||
| + | |- | ||
| + | |SCHEMA | ||
| + | |LIBRARY<br>COLLECTION | ||
| + | |- | ||
| + | |LOG | ||
| + | |JOURNAL | ||
| + | |- | ||
| + | |ISOLATION LEVEL | ||
| + | |COMMITMENT CONTROL LEVEL | ||
| + | |} | ||
| + | |||
| + | <ref>[http://www-03.ibm.com/servers/enable/site/bi/strategy/index.html Business Intelligence Indexing and statistics strategies for DB2 UDB for iSeries] by Michael W. Cain | ||
| + | iSeries Teraplex Integration Center, December 2003, Version 3.0, {{PDF|http://www-03.ibm.com/servers/enable/site/bi/strategy/strategy.pdf}}</ref> | ||
==Tips== | ==Tips== | ||
| Line 16: | Line 47: | ||
;RUNSQL command for ad-hoc SQL statements | ;RUNSQL command for ad-hoc SQL statements | ||
:[http://www.mcpressonline.com/mc/.6b4f1e2e "Partner TechTip: Blend SQL and RPGLE to Make Better Tools"] by [http://www.mcpressonline.com/mc/224@@5c0085ad@1@@.6b4f1e2e Kevin Forsythe] | :[http://www.mcpressonline.com/mc/.6b4f1e2e "Partner TechTip: Blend SQL and RPGLE to Make Better Tools"] by [http://www.mcpressonline.com/mc/224@@5c0085ad@1@@.6b4f1e2e Kevin Forsythe] | ||
| + | |||
| + | ==References== | ||
| + | {{reflist}} | ||
| + | |||
| + | ==See also== | ||
| + | {{Seealso|SQL Primer|SOUNDEX}} | ||
| + | {{SeealsoWP|SQL}} | ||
==Categories== | ==Categories== | ||
Revision as of 20:16, 11 March 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 standard SQL.
Terminology
| SQL Term | iSeries Term |
|---|---|
| TABLW | 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
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
