View source for Access OS/390 DB2 database tables from OS/400 over SNA/APPN

{{FIXME|Solve and document outstanding headscratchers}}
This is a short writeup of how to '''access OS/390 DB2 database tables from OS/400 over SNA/APPN'''. A mandatory requirement is an already established SNA/APPN configuration.

This article assumes OS/390 ADCD 2.10 being used.

== Setup OS/390 IPL parameters ==
Change the configuration parameter: <code>LOADPARM 0A822C..</code> and IPL for an initial cold start handler of DB2.

Subsequent IPLs should use <code>LOADPARM 0A822W..</code> to use the quicker warm start facility.

DB2 parameters are left at default, because we don't know (yet) how to change them.

=== APPN startup summary on OS/390 console ===
This is where I gleaned much of the interesting values.
 - 17.05.53 STC00017  DSNL004I - DDF START COMPLETE
 -            LOCATION  S390LOC
 -            LU        P390LU
 -            GENERICLU -NONE
 -            DOMAIN    -NONE
 -            TCPPORT   446
 -            RESPORT   5020
 - 17.05.53 STC00017  DSN9022I - DSNYASCP 'START DB2' NORMAL COMPLETION
 - 17.05.53 STC00024  +DSNX964I DSNX9STP THE DB2-ESTABLISHED STORED
 -  PROCEDURES ADDRESS SPACE
 -  FOR SUBSYSTEM DSN1 IS STARTED

* S390LOC is (also) the default database name,
* P390LU is the network entity (LUname) name we must connect to.

=== FIXME: How to handle this WTO? ===
 *17.05.41 STC00017 *DSNT405E - DSNTLIDE DISPATCH PRIORITIES NOT IN SYNC:
 * IRLM    : 0020 COMPARED TO DB2     : 0021

* Information but no solution: [https://www.ibm.com/docs/en/db2-for-zos/11?topic=messages-dsnt405e DSNT405E csect-name DISPATCH PRIORITIES NOT IN SYNC: ccccccc1: hhh1 COMPARED TO ccccccc2: hhh2]
* "The internal resource lock manager (IRLM) subsystem manages Db2 locks": [https://www.ibm.com/docs/en/db2-for-zos/12?topic=connections-controlling-irlm Controlling the IRLM]
* What can be done about this?

== Use interactive SQL ==
To get ISPF menus related to DB, use <code>DBSPROC</code> instead of default <code>ISPFPROC</code> as logon proc at logon time.

The interactive SQL facility can be reached:
* Type <code>M.15</code> from the ISPF main menu,
* Type <code>2.1</code> in the subsequent *DB2 Administration Menu*.

Unfortunately, this can't be done in one go.

Below the ''SQL statement:'' line on the appearing screen, SQL commands can be entered. Example:
* <code>CREATE DATABASE foobar</code>
* <code>CREATE TABLE foobar.testtbl (id char(24))</code>
* <code>INSERT INTO foobar.testtbl VALUES ('barbaz')</code>
* <code>SELECT * FROM foobar.testtbl</code>

== Retrieving information about available databases ==
The tool is named DB2I. You can reach it from ISPF main menu:
* Type <code>M.13</code> in ISPF,
* Type <code>D</code> into the the subsequent ''DB2I PRIMARY OPTION MENU'''s command line,
* Change <code>DB2 NAME</code> to be ''DSN1'',
* Press <code>Enter</code> twice,
* Type <code>7</code> in the subsequent ''DB2I PRIMARY OPTION MENU'',
* Choose any predefined ''Cmd x'' line, and enter:
 -DISPLAY DATABASE(*) LIMIT(1000)
* Adjust ''LIMIT'' as needed,
* Press <code>Enter</code> to see a list of defined databases.

Output shows ''NO SPACES FOUND'' for our database from above. Do we need to create/assign separate table space first?

== Remote connection from OS/400 ==
To access remote databases, an according entry in the remote database directory needs to be created.
 addrdbdire rdb(s390loc) rmtlocname(p390lu *sna) dev(p390lu) lcllocname(*netatr) rmtnetid(*netatr) mode(ibmrdb) text('Default DB2 instance on OS/390 ADCD')
OS/400 creates an according device description upon the first connection request.

From <code>strsql</code>, a remote connection can now be initiated:
 CONNECT TO S390LOC USER P390 USING 'P390'

<blockquote>'''Note:''' Use your actual password for the P390 user.</blockquote>

From the SQL prompt you can then issue the usual SQL commands, such as ''SELECT''ing the record inserted above:
 SELECT * FROM foobar.testtbl

== Further clarifications needed ==
* After inserting records from OS/400, and ''SELECT''ing them in OS/390, the session hangs. A small window appears at bottom, stating <code>SQL cost=2</code>. Pressing <code>PA1</code> ends the session with the interactive SQL facility but doesn't resolve the issue.
** Apparently ''SQL cost'' is an estimate for the database management but not an actual error. Many operations in the DB2 Admin application seem to just hang until an eventual timeout with respective WTO output and messages on screen.
* Which parameters are needed for <code>crtddmf</code> on OS/400 to access remote DB2 tables? Example (not working, need to clarify about possible <code>'P390.DATABASE.FILE'</code>):
 crtddmf file(qgpl/p390db) rmtfile(*nonstd 'P390.DATABASE.FILE') rmtlocname(p390lu) dev(p390lu) lcllocname(*netatr) mode(ibmrdb) rmtnetid(*netatr)
** Further research indicates that there might not be a counterpart for DDM access in DB2, but a ''Distributed FileManager/MVS'' component for accessing files in the data set level.
* ''NO SPACES FOUND'', see above. Possible clues:
** [https://www.ibm.com/docs/en/db2-for-zos/12?topic=messages-dsnt602i DSNT602I NO SPACES FOUND], Db2 for z/OS, IBM
** [http://www.ustools.sys.eds.com/usercomms/view.php?bn=usercommunications_interactives&key=1444666587 Usercomm - DB2 V10 Hold Data and Cover Letters for HP Release level AI00], search for ''UI22580 COVER LETTER START''. 

== Weblinks ==
* [https://www.ibm.com/resources/publications/OutputPubsDetails?PubID=SG24620400 S/390 Partners in Development: OS/390 (and z/OS) New Users Cookbook], SG24-6204-00
* [https://www.youtube.com/watch?v=D427APErjcc Mainframe DB2] (YouTube video) by racingmars

[[Category: Software Configuration Guide]]‏
[[Category: System Administration‏‎]]