Difference between revisions of "DB2"

From MidrangeWiki
Jump to: navigation, search
(→‎Terminology Navigation)
(→‎Services: +SERVICE_INFO)
 
(32 intermediate revisions by 5 users not shown)
Line 1: Line 1:
−
[[Category:Database]]
+
{{AN}}, alternately named '''Data Base 2 for AS/400''', also called '''DB2/400'''<ref>http://systeminetwork.com/article/short-history-db2-world</ref>, is the database built into [[OS/400]]. The name was part of the August 1994 release of [[V3R1]].  The current name is [https://www-03.ibm.com/systems/power/software/i/db2/ ''DB2 for i'']
−
DB2 400 is the database built in to [[OS/400]].
+
{{SeeAlsoWP|DB2}}
−
 
 
−
Also see DB2 article at [Wikopedia http://en.wikipedia.org/wiki/DB2]
 
  
 
== Data Base Access ==
 
== Data Base Access ==
Line 16: Line 14:
 
Adding a new file is like adding a new program, only conceptually more simple minded.  We use a structured language to define the layout, and if logical file include what contents in what sequence, then we compile the object.
 
Adding a new file is like adding a new program, only conceptually more simple minded.  We use a structured language to define the layout, and if logical file include what contents in what sequence, then we compile the object.
  
−
The 400 comes with various commands that permit us to dump or display the contents of files various ways.
+
The system comes with various commands that permit us to dump or display the contents of files various ways.
  
 
== Terminology Structure Navigation ==
 
== Terminology Structure Navigation ==
−
 
 
* Physical File = [[SQL]] Table
 
* Physical File = [[SQL]] Table
 
** Individual Records = [[SQL]] Row
 
** Individual Records = [[SQL]] Row
Line 30: Line 27:
 
** Many [[BPCS]] Files use Members to organize different aspects of some application
 
** Many [[BPCS]] Files use Members to organize different aspects of some application
 
*** Customer Orders, RMAs (Returns from Customers0, "Quotes" where there is a tentative order not yet approved ... they all use same file name layout, but a different member for function
 
*** Customer Orders, RMAs (Returns from Customers0, "Quotes" where there is a tentative order not yet approved ... they all use same file name layout, but a different member for function
−
* Logical File = Access Path
+
* Logical File = Access Path = [[SQL]] Logical View
 +
** Some languages and tools have choice of pointing to one logical, or one big view (ignore logical boundaries)
 +
** [[RPG]] thinks it is accessing the whole file, but we control which member via the [[CLP]] that calls the RPG Program, at run time
 +
 
 +
=== SOURCE - PHYSICAL FILE ===
 +
Typically we edit source code using [[PDM]] / [[SEU]] which can also be used to access other types of physical files.
 +
 
 +
* Source code for programs is in a physical file in which each individual program is in a different member of that file
 +
* [[IBM]] recommends source file name structure Q language SRC but we are not bound by this
 +
** QBASSRC for [[BASIC]] programs
 +
** QCBLSRC for [[COBOL]] programs
 +
** QCLBLLESRC for [[ILE COBOL]] programs
 +
** QCLSRC for [[CLP]] programs
 +
** QCMDSRC for Command Definition
 +
** Communication Files
 +
** QDKTSRC diskette device file specifications
 +
*** Who writes for diskette these days?
 +
** QDDSSRC for [[DDS]] used in defining file layouts, physical and logical files, printer output files, interactive screen displays
 +
*** We might want to organize the specifications into a separate Q file for files, help screens, interactive screens, prompt screens, report output, even though they might all be created with DDS
 +
**** QFMTSRC for Format and Reformat
 +
**** Menus might be in QMNUSRC
 +
** Menus might be in QSDASRC
 +
** QPASSRC for [[Pascal]]
 +
** QPL1SRC for [[PL/1]]
 +
** QPNLSRC is where [[BPCS]] places [[UIM]] source for panels such as HELP and MENUs
 +
** QRJXSRC for [[RJE]] Remote Job Entry specifications
 +
** QRPGSRC for [[RPG/400]] programs
 +
** QRPGLESRC for ILE [[RPG 4]]
 +
** QS36SRC for [[S/36]] Environment
 +
** QTAPSRC for tape device file specifications
 +
*** We not need this for backup because the act of copying IBM i files to some other name or place, the external description travels along with the data records
 +
** QTBLSRC for Translate Tables
 +
** QTXTSRC for documentation
 +
** QUDSSRC for [[IDU]] applications
 +
* The format of each Q*SRC contains the rules for the language involved
 +
** How big a line #
 +
** remember date last change
 +
** actual data
 +
** programming language source of rules
 +
*** This gets us automatic syntax checking during interactive program editing
 +
 
 +
Organizing software on the system this way, makes it easier to import similar code.
 +
The Data records of each program member can be treated like data by the program compiler, and various [[400 languages and database tools]]
 +
 
 +
=== Mapping Relationships ===
 +
* [[DSPDBR]] = Display, or Print, Data Base relations
 +
** ie. what are all the logicals that point at this physical file
 +
* [[DSPPGMREF]] = Display, or Print, all the objects accessed by this program
 +
** not a pretty picture
 +
** We can send output of *ALL programs to an *OUTFILE then access it via [[Query]] or programming language
 +
*** also not a pretty picture if different programs access same physical file using different logicals
 +
 
 +
In addition to what comes with [[IBM]] [[ADT]] Tool Set, there are Third Party sources of tools to help us make sense of what all is in our data base and software collection, and what their interrelationships are
 +
* [http://www.hawkinfo.com Hawkeye]
 +
* [http://www.asc-iseries.com Abstract/Probe]
 +
 
 +
=== Temporary Access Paths ===
 +
There is a performance hit when we have many different logical views of a physical file, since they all have to be updated when there is any change to the data contents of the physical file.
 +
 
 +
If there are logical views that are rarely needed, consider wiping them out after the run that needs them, and adding them at beginning of same run, so they are not a drain on the system when not needed.
  
 
== Features of DB2 ==
 
== Features of DB2 ==
 +
Compared to how files could be structured and accessed on prior [[IBM]] and other platforms, DB2 had several revolutionary features that added to the potential programming power of the system.
  
−
Compared to how files could be structured and accessed on prior [[IBM]] and other platforms, DB2 had several revolutionary features that added to the potential programming power of the 400.
+
Some DB2 capabilities, that we take for granted today on the system, may have been available on [[S/38]] but not on [[S/36]] [[SSP]].
−
 
 
−
Some DB2 capabilities, that we take for granted today on the 400, may have been available on [[S/38]] but not on [[S/36]] [[SSP]].
 
  
 
* DB2 defines data at the system level
 
* DB2 defines data at the system level
Line 46: Line 101:
 
** Triggers
 
** Triggers
 
** Stored Procedures
 
** Stored Procedures
 +
** Queryable [[DB2_catalog|system catalog]]
  
 
=== External Definition ===
 
=== External Definition ===
−
 
 
Before the invention of this data base reality, we could only use Internal Definition of files.  That means each and every program had to have its own layout of a file, and any dumps of the file showed all the data contigous, not breaking it up into meaningful fields.  Forget about being able to access the data with programs like [[SQL]].
 
Before the invention of this data base reality, we could only use Internal Definition of files.  That means each and every program had to have its own layout of a file, and any dumps of the file showed all the data contigous, not breaking it up into meaningful fields.  Forget about being able to access the data with programs like [[SQL]].
  
Line 58: Line 113:
  
 
==== Internal Definition ====
 
==== Internal Definition ====
−
 
+
Prior to External Definition, the Traditional way to define files was by describing them within Programs.
−
Prior to External Definition, tthe Traditional way to define files was by describing them within Programs.
 
  
 
Each program would have line for line record layout.
 
Each program would have line for line record layout.
  
−
This ran risks of mistakes by too many cooks, and was a more tedious way to program, except via /COPY layout from some library of layouts.
+
This ran risks of mistakes by too many cooks, and was a more tedious way to program, except via [[Slash COPY|/COPY]] layout from some library of layouts.
  
 
If there was a change needed in file layout, like adding another field, we had to change it in every program, and compile all of them.  Even though most programs not using THAT new field, there was the issue of conflict with definition of size of file.
 
If there was a change needed in file layout, like adding another field, we had to change it in every program, and compile all of them.  Even though most programs not using THAT new field, there was the issue of conflict with definition of size of file.
Line 70: Line 124:
  
 
==== External Advantages ====
 
==== External Advantages ====
−
 
 
By having Data Definition as part of the file itself, in addition to the Dta Records, each program need only identify the file, and the layout gets sucked into the program at compile time.
 
By having Data Definition as part of the file itself, in addition to the Dta Records, each program need only identify the file, and the layout gets sucked into the program at compile time.
  
 
Move or copy an object, and its Descriptive Part moves along with the Data Records.
 
Move or copy an object, and its Descriptive Part moves along with the Data Records.
 +
 +
 +
== Services ==
 +
IBM are providing the community with a variety of catalogs and services.
 +
Some of these services are delivered via the Db2 PTF Group, some come from Technology Refreshes, and some come from full releases of IBM i.
 +
 
 +
* Catalogs are system-supplied views that can be queried to interrogate the state of the database.  For example, SYSTABLES is a list of tables.
 +
* Services are SQL statements that provide a service - similar to an API call.  An example is DELIMIT_NAME, which wraps a string in double quotes.  The documentation for services separates them into two classes: Services for Db2, and services for IBM i.
 +
* Find documentation in the Knowledge Center under Database > Performance and query optimization > [https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqservicesdb2.htm DB2 for i Services] (or [https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqservicessys.htm IBM i Services])
 +
* There is a PDF overview of Db2 and IBM i services at [http://www.ibm.com/developerworks/ibmi/techupdates/db2/landscape Db2 Landscape]
 +
* Additional (often early) documentation can be found in [http://ibm.biz/DB2foriServices Developerworks Db2 for i Services]
 +
 +
IBM have provided a view in QSYS2 called SERVICES_INFO (available 7.1 and up) that describes each of the available services for your specific machine.
 +
<pre>
 +
select *
 +
from services_info
 +
order by service_category, service_name;
 +
</pre>
 +
 +
=== DB2 for i Services ===
 +
 +
==== Application Services ====
 +
These procedures provide interfaces that are useful for application development.
 +
 +
{| class="wikitable sortable"
 +
|-
 +
! Name
 +
! Type
 +
! Purpose
 +
|-
 +
| DELIMIT_NAME
 +
| Scalar function
 +
| Returns a name with delimiters if the delimiters are needed for use in an SQL statement.
 +
|-
 +
| OVERRIDE_QAQQINI
 +
| Procedure
 +
| Creates and modifies a temporary version of the QAQQINI file.
 +
|-
 +
| OVERRIDE_TABLE
 +
| Procedure
 +
| Sets the blocking size for a table.
 +
|-
 +
| PARSE_STATEMENT
 +
| Table function
 +
| Returns a list of object and column names that are used in an SQL query, data change statement, or other statement where a query or expression is specified
 +
|-
 +
| WLM_SET_CLIENT_INFO
 +
| Procedure
 +
| Sets values for the SQL client special registers.
 +
|}
 +
 +
==== Performance Services ====
 +
These services include procedures that provide interfaces to work with indexes and a view to see information about database monitors.
 +
 +
==== Plan Cache Services ====
 +
These services include procedures to assist you in performing database administration (DBA) and database engineering (DBE) tasks.
 +
 +
==== Utility Services ====
 +
These procedures provide interfaces to monitor and work with SQL in jobs on the current system or to compare constraint and routine information across systems.
 +
 +
 +
=== IBM i Services ===
 +
 +
==== Application Services ====
 +
These procedures and views provide interfaces that can be used in applications.
 +
 +
==== Communication Services ====
 +
These views and procedure provide communication information.
 +
 +
==== Java Services ====
 +
This view and procedure provide Java information and JVM management options.
 +
 +
==== Journal Services ====
 +
This function and view provide journal information.
 +
 +
==== Librarian Services ====
 +
These services provide object and library list information.
 +
 +
{| class="wikitable sortable"
 +
|-
 +
! Name
 +
! Type
 +
! Purpose
 +
|-
 +
| LIBRARY_LIST_INFO
 +
| View
 +
| Contains information about the current job's library list.
 +
|-
 +
| OBJECT_STATISTICS
 +
| Table function
 +
| Returns information about objects in a library.
 +
|}
 +
 +
==== Message Handling Services ====
 +
These views provide system message information.
 +
 +
==== Product Services ====
 +
These services provide information about licensed products.
 +
 +
==== PTF Services ====
 +
These views provide PTF information.
 +
 +
==== Security Services ====
 +
These views, procedures, and functions provide security information.
 +
 +
==== Spool Services ====
 +
This view and function provide information about spooled files.
 +
 +
==== Storage Services ====
 +
These views provide information about storage and storage devices.
 +
 +
==== System Health Services ====
 +
For the most important system resources, the IBM i operating system automatically tracks the highest consumption and consumers.
 +
 +
==== Work Management Services ====
 +
These views and functions provide system value and job information.
 +
 +
 +
=== SYSTOOLS ===
 +
[https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqsystools.htm SYSTOOLS] is a set of DB2® for IBM® i supplied examples and tools.
 +
 +
It is the intention of IBM to add content dynamically to SYSTOOLS, either on base releases or through PTFs for field releases. A best practice for customers who are interested in such tools would be to periodically review the contents of SYSTOOLS.
 +
 +
The tools in SYSTOOLS are not documented in the Knowledge Center.  The documentation is scattered among various articles, and [https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/New%20HTTP%20functions%20added%20to%20SYSTOOLS DeveloperWorks]
 +
 +
==== HTTPGETCLOB ====
 +
 +
==References==
 +
{{reflist}}
 +
 +
==Categories==
 +
[[Category:Database]]
 +
[[Category:Definitions]]

Latest revision as of 13:55, 31 March 2020

DB2, alternately named Data Base 2 for AS/400, also called DB2/400[1], is the database built into OS/400. The name was part of the August 1994 release of V3R1. The current name is DB2 for i

See also on Wikipedia: DB2

Data Base Access

It is normally accessed directly from a high level language such as SQL, or a Data Base Tool like Query.

New files may be added via

Adding a new file is like adding a new program, only conceptually more simple minded. We use a structured language to define the layout, and if logical file include what contents in what sequence, then we compile the object.

The system comes with various commands that permit us to dump or display the contents of files various ways.

Terminology Structure Navigation

  • Physical File = SQL Table
    • Individual Records = SQL Row
    • Fields of the File = SQL Column
  • In addition to the Data Records
  • Format
    • A File can have more than one Format, or layout, but not all programming languages can cope with this
  • Members
    • A file can have more than one collection of records, all sharing the same file characteristics, but with a different name, and not all programming languages can cope with this
    • Many BPCS Files use Members to organize different aspects of some application
      • Customer Orders, RMAs (Returns from Customers0, "Quotes" where there is a tentative order not yet approved ... they all use same file name layout, but a different member for function
  • Logical File = Access Path = SQL Logical View
    • Some languages and tools have choice of pointing to one logical, or one big view (ignore logical boundaries)
    • RPG thinks it is accessing the whole file, but we control which member via the CLP that calls the RPG Program, at run time

SOURCE - PHYSICAL FILE

Typically we edit source code using PDM / SEU which can also be used to access other types of physical files.

  • Source code for programs is in a physical file in which each individual program is in a different member of that file
  • IBM recommends source file name structure Q language SRC but we are not bound by this
    • QBASSRC for BASIC programs
    • QCBLSRC for COBOL programs
    • QCLBLLESRC for ILE COBOL programs
    • QCLSRC for CLP programs
    • QCMDSRC for Command Definition
    • Communication Files
    • QDKTSRC diskette device file specifications
      • Who writes for diskette these days?
    • QDDSSRC for DDS used in defining file layouts, physical and logical files, printer output files, interactive screen displays
      • We might want to organize the specifications into a separate Q file for files, help screens, interactive screens, prompt screens, report output, even though they might all be created with DDS
        • QFMTSRC for Format and Reformat
        • Menus might be in QMNUSRC
    • Menus might be in QSDASRC
    • QPASSRC for Pascal
    • QPL1SRC for PL/1
    • QPNLSRC is where BPCS places UIM source for panels such as HELP and MENUs
    • QRJXSRC for RJE Remote Job Entry specifications
    • QRPGSRC for RPG/400 programs
    • QRPGLESRC for ILE RPG 4
    • QS36SRC for S/36 Environment
    • QTAPSRC for tape device file specifications
      • We not need this for backup because the act of copying IBM i files to some other name or place, the external description travels along with the data records
    • QTBLSRC for Translate Tables
    • QTXTSRC for documentation
    • QUDSSRC for IDU applications
  • The format of each Q*SRC contains the rules for the language involved
    • How big a line #
    • remember date last change
    • actual data
    • programming language source of rules
      • This gets us automatic syntax checking during interactive program editing

Organizing software on the system this way, makes it easier to import similar code. The Data records of each program member can be treated like data by the program compiler, and various 400 languages and database tools

Mapping Relationships

  • DSPDBR = Display, or Print, Data Base relations
    • ie. what are all the logicals that point at this physical file
  • DSPPGMREF = Display, or Print, all the objects accessed by this program
    • not a pretty picture
    • We can send output of *ALL programs to an *OUTFILE then access it via Query or programming language
      • also not a pretty picture if different programs access same physical file using different logicals

In addition to what comes with IBM ADT Tool Set, there are Third Party sources of tools to help us make sense of what all is in our data base and software collection, and what their interrelationships are

Temporary Access Paths

There is a performance hit when we have many different logical views of a physical file, since they all have to be updated when there is any change to the data contents of the physical file.

If there are logical views that are rarely needed, consider wiping them out after the run that needs them, and adding them at beginning of same run, so they are not a drain on the system when not needed.

Features of DB2

Compared to how files could be structured and accessed on prior IBM and other platforms, DB2 had several revolutionary features that added to the potential programming power of the system.

Some DB2 capabilities, that we take for granted today on the system, may have been available on S/38 but not on S/36 SSP.

  • DB2 defines data at the system level
    • This minimizes / eliminates data redundancy
    • Any number of different 400 languages and database tools can access the same file layout, without needing definition within that software
  • DB2 supports
    • Data Sharing
    • Referential Integrity
    • Triggers
    • Stored Procedures
    • Queryable system catalog

External Definition

Before the invention of this data base reality, we could only use Internal Definition of files. That means each and every program had to have its own layout of a file, and any dumps of the file showed all the data contigous, not breaking it up into meaningful fields. Forget about being able to access the data with programs like SQL.

With Internal Definition, each program only needed to define the fields it was going to use, which meant there might not be any one place to go for the complete layout of a file.

With External Definition, part of the DB2 file structure is the whole story on the file and its field layout, which can be accessed by any programming language in the IBM family.

Internal Definition is still supported, and we can use it in a program to break an External Definition field down into smaller sub-fields such as through a Data Structure.

Internal Definition

Prior to External Definition, the Traditional way to define files was by describing them within Programs.

Each program would have line for line record layout.

This ran risks of mistakes by too many cooks, and was a more tedious way to program, except via /COPY layout from some library of layouts.

If there was a change needed in file layout, like adding another field, we had to change it in every program, and compile all of them. Even though most programs not using THAT new field, there was the issue of conflict with definition of size of file.

20-30 % of COBOL code was this stuff, with a lesser impact on RPG

External Advantages

By having Data Definition as part of the file itself, in addition to the Dta Records, each program need only identify the file, and the layout gets sucked into the program at compile time.

Move or copy an object, and its Descriptive Part moves along with the Data Records.


Services

IBM are providing the community with a variety of catalogs and services. Some of these services are delivered via the Db2 PTF Group, some come from Technology Refreshes, and some come from full releases of IBM i.

  • Catalogs are system-supplied views that can be queried to interrogate the state of the database. For example, SYSTABLES is a list of tables.
  • Services are SQL statements that provide a service - similar to an API call. An example is DELIMIT_NAME, which wraps a string in double quotes. The documentation for services separates them into two classes: Services for Db2, and services for IBM i.
  • Find documentation in the Knowledge Center under Database > Performance and query optimization > DB2 for i Services (or IBM i Services)
  • There is a PDF overview of Db2 and IBM i services at Db2 Landscape
  • Additional (often early) documentation can be found in Developerworks Db2 for i Services

IBM have provided a view in QSYS2 called SERVICES_INFO (available 7.1 and up) that describes each of the available services for your specific machine. 

select *
from services_info
order by service_category, service_name;

DB2 for i Services

Application Services

These procedures provide interfaces that are useful for application development.

Name Type Purpose
DELIMIT_NAME Scalar function Returns a name with delimiters if the delimiters are needed for use in an SQL statement.
OVERRIDE_QAQQINI Procedure Creates and modifies a temporary version of the QAQQINI file.
OVERRIDE_TABLE Procedure Sets the blocking size for a table.
PARSE_STATEMENT Table function Returns a list of object and column names that are used in an SQL query, data change statement, or other statement where a query or expression is specified
WLM_SET_CLIENT_INFO Procedure Sets values for the SQL client special registers.

Performance Services

These services include procedures that provide interfaces to work with indexes and a view to see information about database monitors.

Plan Cache Services

These services include procedures to assist you in performing database administration (DBA) and database engineering (DBE) tasks.

Utility Services

These procedures provide interfaces to monitor and work with SQL in jobs on the current system or to compare constraint and routine information across systems.


IBM i Services

Application Services

These procedures and views provide interfaces that can be used in applications.

Communication Services

These views and procedure provide communication information.

Java Services

This view and procedure provide Java information and JVM management options.

Journal Services

This function and view provide journal information.

Librarian Services

These services provide object and library list information.

Name Type Purpose
LIBRARY_LIST_INFO View Contains information about the current job's library list.
OBJECT_STATISTICS Table function Returns information about objects in a library.

Message Handling Services

These views provide system message information.

Product Services

These services provide information about licensed products.

PTF Services

These views provide PTF information.

Security Services

These views, procedures, and functions provide security information.

Spool Services

This view and function provide information about spooled files.

Storage Services

These views provide information about storage and storage devices.

System Health Services

For the most important system resources, the IBM i operating system automatically tracks the highest consumption and consumers.

Work Management Services

These views and functions provide system value and job information.


SYSTOOLS

SYSTOOLS is a set of DB2® for IBM® i supplied examples and tools.

It is the intention of IBM to add content dynamically to SYSTOOLS, either on base releases or through PTFs for field releases. A best practice for customers who are interested in such tools would be to periodically review the contents of SYSTOOLS.

The tools in SYSTOOLS are not documented in the Knowledge Center. The documentation is scattered among various articles, and DeveloperWorks

HTTPGETCLOB

References

  1. ↑ http://systeminetwork.com/article/short-history-db2-world

Categories

Retrieved from "https://wiki.midrange.com/index.php?title=DB2&oldid=13652"