Acessando o DB2 no Hercules z/OS

Os processos do z/OS são separados em regiões que são chamados address spaces. Os processos do DB2 z/OS são executados em diversos address spaces diferentes. Alguns processos de aplicações do DB2 rodam em um address space que solicitam serviços ao DB2 como WebSphere, IMS, e CICS. Os utilitários do DB2 rodam no ambiente batch do z/OS. Aplicações que acessam recursos do DB2 podem rodar em um mesmo sistema z/OS no CICS, IMS, TSO ou em ambiente batch ou em outro sistema operacional. Essas aplicações podem acessar recursos do DB2 usando serviços cliente/servidor do DB2 distribued data facility (DDF). A IBM disponibiliza ‘attachment facilities’ para conectar o DB2 em cada um desses ambientes.

Checando se o DB2 esta online

Na console de o comando D A,L para ver todos os aplicativos, se aparecer algum com o nome DB9 estes são o db2 rodando no nosso Hercules;

    - 16.01.04           d a,l
      16.01.04           IEE114I 16.01.04 2016.054 ACTIVITY 107               C
       JOBS     M/S    TS USERS    SYSAS    INITS   ACTIVE/MAX VTAM     OAS
      00003    00019    00002      00032    00013    00002/00040       00012
       LLA      LLA      LLA      NSW  S  JES2     JES2     IEFPROC  NSW  S
       VLF      VLF      VLF      NSW  S  VTAM     VTAM     VTAM     NSW  S
       DLF      DLF      DLF      NSW  S  RACF     RACF     RACF     NSW  S
       RRS      RRS      RRS      NSW  S  TSO      TSO      STEP1    OWT  S
       SDSF     SDSF     SDSF     NSW  S  TCPIP    TCPIP    TCPIP    NSW  SO
       TN3270   TN3270   TN3270   NSW  SO HTTPD1   HTTPD1   WEBSRV1  IN   SO
       NFSS     NFSS     GFSAMAIN NSW  SO CSQ7MSTR CSQ7MSTR PROCSTEP NSW  S
       INETD4   STEP1    OMVSKERN OWT  AO PORTMAP  PORTMAP  PMAP     OWT  SO
       FTPD1    STEP1    FTPD     OWT  AO SSHD4    STEP1    START2   OWT  AO
       DB9GMSTR DB9GMSTR IEFPROC  NSW  S  DB9GIRLM DB9GIRLM          NSW  S
       DB9GDBM1 DB9GDBM1 IEFPROC  NSW  S  DB9GDIST DB9GDIST IEFPROC  NSW  SO
      SYSADM  OWT      IBMUSER OWT




  IEE612I CN=L700     DEVNUM=0700 SYS=ADCD
  D A,L

  IEE163I MODE= RD

Se não tiver online de o comando -DB9G START DB2 que ele vai ficar online;

Acessando pelo Hercules o DB2

------------------------------- TSO/E LOGON -----------------------------------


   Enter LOGON parameters below:                   RACF LOGON parameters:

   Userid    ===> SYSADM

   Password  ===>                                  New Password ===>

   Procedure ===> DBSPROC9                         Group Ident  ===>

   Acct Nmbr ===> ACCT#

   Size      ===> 4098

   Perform   ===>

   Command   ===> ISPF

   Enter an 'S' before each option desired below:
           -Nomail         -Nonotice        -Reconnect        -OIDcard

PF1/PF13 ==> Help    PF3/PF15 ==> Logoff    PA1 ==> Attention    PA2 ==> Reshow
You may request specific help information by entering a '?' in any entry field

Tela de boas vindas do TSO;

IKJ56455I SYSADM LOGON IN PROGRESS AT 21:33:59 ON NOVEMBER 3, 2015
IKJ56951I NO BROADCAST MESSAGES
*****************************************************************
*                                                               *
* APPLICATION DEVELOPER'S CONTROLLED DISTRIBUTION (ADCD)        *
*                                                               *
*  ADCD.Z110.CLIST(ISPFCL) PRODUCES THIS MESSAGE                *
*  ADCD.* DATASETS CONTAIN SYSTEM CUSTOMIZATION                 *
*  SMP/E DATASETS CAN BE LOCATED FROM 3.4 WITH DSNAME **.CSI    *
*  HTTP://DTSC.DFW.IBM.COM/ADCD.HTML CONTAINS DOCUMENTATION     *
*                                                               *
*  USERID              PASSWORD        COMMENT                  *
*  ----------------    ------------ --------------              *
*  IBMUSER             - SYS1/IBMUSER FULL AUTHORITY            *
*  ADCDMST             - ADCDMST      FULL AUTHORITY            *
*  ADCDA THRU ADCDZ    - TEST         LIMITED AUTHORITY(NO OMVS)*
*  OPEN1 THRU OPEN3    - SYS1         UID(0) (NO TSO)           *
*                                                               *
*****************************************************************

READY
ISPF

Digito M para ver mais opções

  Menu  Utilities  Compilers  Options  Status  Help

                           ISPF Primary Option Menu

0  Settings      Terminal and user parameters            User ID . : SYSADM
1  View          Display source data or listings         Time. . . : 21:34
2  Edit          Create or change source data            Terminal. : 3278
3  Utilities     Perform utility functions               Screen. . : 1
4  Foreground    Interactive language processing         Language. : ENGLISH
5  Batch         Submit job for language processing      Appl ID . : ISR
6  Command       Enter TSO or Workstation commands       TSO logon : DBSPROC
7  Dialog Test   Perform dialog testing                  TSO prefix: SYSADM
9  IBM Products  IBM program development products        System ID : ADCD
                                                r        MVS acct. : ACCT#
 Licensed Materials - Property of IBM                    Release . : ISPF 6.0
 5694-A01     Copyright IBM Corp. 1980, 2008.
 All rights reserved.
 US Government Users Restricted Rights -
 Use, duplication or disclosure restricted      s
 by GSA ADP Schedule Contract with IBM Corp.

Option ===> M

Feito, aparece o menu com a opção para o DB2 - Digito 16 (enter);

   Menu  Help
 ------------------------------------------------------------------------------
                            IBM Products Panel
                                                                    More:     +

 1  SMP/E       System Modification Program/Extended
 2  ISMF        Integrated Storage Management Facility
 3  RACF        Resource Access Control Facility
 4  HCD         Hardware Configuration Dialogs
 5  SDSF        Spool Search and Display Facility
 6  IPCS        Interactive Problem Control System
 7  DITTO       DITTO/ESA for MVS Version 1
 8  RMF         Resource Measurement Facility
 9  DFSORT      Data Facility Sort
 10 OMVS        MVS OpenEdition
 11 MQ          WMQ Series Operations and Control
 12 WLM         Workload Manager
 13 FMN         File Manager 0perations and Control
 14 RRS         Resource Recovery Services
 15 DB2 V9      DB2 9.1.0 Must use volume S9DB91 & TSOPROC DBSPROC9
 16 DB2ADM9     Data Base Admin Tool 7.2.0
 Option ===> 16
  F1=Help      F2=Split     F3=Exit      F7=Backward  F8=Forward   F9=Swap
 F10=Actions  F12=Cancel

Este erro pesquisei mais não consegui descobrir resolver ele, só tecle enter para passar, se alguém souber me diga nos comentários;

 DB2 Admin ------------ DB2 Admin DB2 Subsystem Selection Exit Panel ----------
+-------------------------------------------------------------------------------+
| DB2 Admin ----------------------- Verify CCSID ----------------------- 14:23  |
|                                                                               |
| Warning: DB2 data corruption can result from this DB2 Administration Tool     |
| session because the CCSID used by the terminal is not the same as the CCSID   |
| used by DB2 Admin. Notify the DB2 Admin administrator.                        |
|                                                                               |
|          - Terminal CCSID       :00037                                        |
|          - Admin Tool plan CCSID:01047                                        |
|                                                                               |
| Press: ENTER to continue or END to exit                                       |
|  F1=HELP     F2=SPLIT    F3=END      F4=RETURN   F5=RFIND    F6=RCHANGE       |
|  F7=UP       F8=DOWN     F9=SWAP    F10=LEFT    F11=RIGHT                     |
+-------------------------------------------------------------------------------+
+-------------------------------------------------------------------------------+
| Database 2 Administration Tool.                                               |
| 5697-L90 (C) Copyright IBM Corporation 1995, 2006.                            |
| All rights reserved.  Licensed materials - property of IBM.                   |
| US Government Users Restricted Rights - Use, duplication or disclosure        |
| restricted by GSA ADP schedule contract with IBM Corp.                        |
+-------------------------------------------------------------------------------+
  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Vamos executar alguns comandos SQL no nosso DB2- escolha a opção 2;

 DB2 Admin -------------- DB2 Administration Menu 7.2.0 ------------------ 14:25
 Option ===> 2

    1 - DB2 system catalog                                DB2 System: DB9G
    2 - Execute SQL statements                            DB2 SQL ID: SYSADM
    3 - DB2 performance queries                           Userid    : SYSADM
    4 - Change current SQL ID                             DB2 Rel   : 915
    5 - Utility generation using LISTDEFs and TEMPLATEs
    P - Change DB2 Admin parameters
   DD - Distributed DB2 systems
    E - Explain
    Z - DB2 system administration
   SM - Space management functions
    W - Manage work statement lists
    X - Exit DB2 Admin


                                                                    More:     +
 Interface to other DB2 products and offerings:
    D  - DB2I


  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Escolha a opção 1 (enter);

 DB2 Admin -------------------- Execute SQL Statements ------------------- 14:25
 Option ===> 1

    1 - Execute SQL statements from screen input          DB2 System: DB9G
    2 - Run or Explain SQL statements                     DB2 SQL ID: SYSADM
    3 - Build SQL SELECT, INSERT, UPDATE or DELETE prototype
    4 - Create/drop/label/comment on objects
    5 - Grant/revoke privileges on objects














  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Este foi o primeiro comando que descobri pra rodar no nosso DB2;

 DB2 Admin ----------- Execute SQL Statements from Screen Input ---------- 14:25
 Command ===>

                                                          DB2 System: DB9G
 SQL statement:                                           DB2 SQL ID: SYSADM
 SELECT * FROM SYSIBM.SYSUSERAUTH ;














                                                                             >
 Press ENTER to execute the SQL statement, or enter EDIT on the command line to
  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Resultado, para sair da tela tecle PF3;

 DB2 Admin -- Browse Result of SQL Select               ----- Row 1 to 12 of 12

 L GRANTOR  GRANTEE  TIMESTAMP    DATEGRANTED TIMEGRANTED GRANTEETYPE AUTHHOWGOT
   *        *        *            *           *           *           *
 - -------- -------- ------------ ----------- ----------- ----------- ----------
   SYSIBM   SYSOPR
   IBMUSER  CCUSER   DAEGIX8E16RT 070402      11550689                S
   IBMUSER  CCUSER   DAEGIYEVHA32 070402      11550821                S
   IBMUSER  CCUSER   DAEGIYEVLKKD 070402      11550822                S
   IBMUSER  DPACK    DAEOOUR4YF1I 070403      07524127                S
   IBMUSER  CLARKG   DAEOOUR54EWB 070403      07524127                S
   IBMUSER  OPER     DAEQZ2ALXPYC 070403      13324529                S
   IBMUSER  OPER     DAEQ3BRLUDST 070403      13421606                S
   DPACK    RGONZAL  DAS2X2HQ1NTJ 070524      13341462                S
   DPACK    CLARKG   DBFXM7FDDFOW 070810      12291885                S
   CLARKG   FMN      DC2DK3XSMU57 080227      13081804                S
   CLARKG   KEMMERT  DDEEFQSENNP3 080410      10271359                S
 ******************************* END OF DB2 DATA *******************************



 Command ===>                                                  Scroll ===> PAGE
  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Vamos rodar outro comando;

 DB2 Admin ----------- Execute SQL Statements from Screen Input ---------- 14:26
 Command ===>

                                                          DB2 System: DB9G
 SQL statement:                                           DB2 SQL ID: SYSADM
 SELECT * FROM SYSIBM.SYSVOLUMES;














                                                                             >
 Press ENTER to execute the SQL statement, or enter EDIT on the command line to
  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Resultado, para sair da tela tecle PF3;

 DB2 Admin -- Browse Result of SQL Select               ----- Row 1 to 12 of 12

 L SGNAME   SGCREATOR VOLID  IBMREQD RELCREATED
   *        *         *      *       *
 - -------- --------- ------ ------- ----------
   AOCOESG  IBMUSER   ZADB91 N
   TESTSG   IBMUSER   ZADB91 N
   ADBGCHG  IBMUSER   ZADB91 N
   ADBGCH   IBMUSER   ZADB91 N
   DSQ1STBG IBMUSER   ZADB91 N
   DSQSGDEF IBMUSER   ZADB91 N
   DSQSGCTL IBMUSER   ZADB91 N
   DSN8G91Y IBMUSER   ZADB91 N
   DSN8G91U IBMUSER   ZADB91 N
   DSN8G910 IBMUSER   ZADB91 N
   DSNOSCSG IBMUSER   ZADB91 N
   SYSDEFLT IBMUSER   ZADB91 N
 ******************************* END OF DB2 DATA *******************************



 Command ===>                                                  Scroll ===> PAGE
  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Tabelas disponíveis consultas, quem quiser checar;

SELECT * FROM SYSIBM.SYSTABLES;

Pesquisando;

 DB2 Admin ----------- Execute SQL Statements from Screen Input ---------- 15:54
 Command ===>

                                                          DB2 System: DB9G
 SQL statement:                                           DB2 SQL ID: SYSADM
 SELECT * FROM SYSIBM.SYSTABLES;














                                                                             >
 Press ENTER to execute the SQL statement, or enter EDIT on the command line to
  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Resultado;

 DB2 Admin -- Browse Result of SQL Select               ---- Row 1 to 16 of 590

 L NAME                           CREATOR  TYPE DBNAME   TSNAME     DBID   OBID
   *                              *        *    *        *             *      *
 - ------------------------------ -------- ---- -------- -------- ------ ------
   SYSOBDS                        SYSIBM   T    DSNDB06  SYSALTER      6    739
   SYSCONTEXTAUTHIDS              SYSIBM   T    DSNDB06  SYSCONTX      6    841
   SYSCTXTTRUSTATTRS              SYSIBM   T    DSNDB06  SYSCONTX      6    837
   SYSCONTEXT                     SYSIBM   T    DSNDB06  SYSCONTX      6    830
   SYSCOPY                        SYSIBM   T    DSNDB06  SYSCOPY       6     46
   SYSFIELDS                      SYSIBM   T    DSNDB06  SYSDBASE      6     21
   SYSTABLESPACE                  SYSIBM   T    DSNDB06  SYSDBASE      6     17
   SYSTABLES                      SYSIBM   T    DSNDB06  SYSDBASE      6     19
   SYSTABLEPART                   SYSIBM   T    DSNDB06  SYSDBASE      6     18
   SYSTABAUTH                     SYSIBM   T    DSNDB06  SYSDBASE      6     28
   SYSFIELDS                      SYSIBMV  V    DSNDB06  SYSDBASE      0      0
   SYSFOREIGNKEYS                 SYSIBMV  V    DSNDB06  SYSDBASE      0      0
   SYSSYNONYMS                    SYSIBM   T    DSNDB06  SYSDBASE      6     33
   SYSRELS                        SYSIBM   T    DSNDB06  SYSDBASE      6     22
   SYSKEYS                        SYSIBM   T    DSNDB06  SYSDBASE      6     27
   SYSINDEXPART                   SYSIBM   T    DSNDB06  SYSDBASE      6     26
 Command ===>                                                  Scroll ===> PAGE
  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Vamos pesquisar a tabela SYSCOPY

 DB2 Admin ----------- Execute SQL Statements from Screen Input ---------- 15:55
 Command ===>

                                                          DB2 System: DB9G
 SQL statement:                                           DB2 SQL ID: SYSADM
 SELECT * FROM SYSIBM.SYSCOPY;














                                                                             >
 Press ENTER to execute the SQL statement, or enter EDIT on the command line to
  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Resultado;

 DB2 Admin -- Browse Result of SQL Select               ---- Row 1 to 16 of 162

 L DBNAME   TSNAME         DSNUM ICTYPE ICDATE START_RBA   FILESEQNO DEVTYPE
   *        *                  * *      *      *                   * *
 - -------- -------- ----------- ------ ------ --------- ----------- --------
   ADBDCHG  ADBSCMSK           0 C      070502   .y.¡              0
   ADBDCHG  ADBSCPRQ           0 C      070502   .w@¤              0
   ADBDCHG  ADBSCHGS           0 C      070502   .tL­              0
   ADBDCHG  ADBSCHG            0 C      070502   .s.Å              0
   ADBDCH   ADBSCH             0 C      070502   .æÛy              0
   DSNATPDB DSNATPTS           0 C      080417   Oû±.              0
   DSNATPDB DSNASDMA           0 C      080417   OúL¥              0
   DSNATPDB DSNASDME           0 C      080417   Oÿ.è              0
   DSNATPDB DSNASDMU           0 C      080417   Oÿ+ð              0
   ADBDCHG  ADBSCIGN           0 C      070502   .¿ß¿              0
   DSNADMDB DSNADMTS           0 C      080417   ¹º[@              0
   ADBDCHG  ADBSCVER           0 C      070502   .®.5              0
   CC390    UTPROC             0 C      070402   .·OA              0
   CC390    UTTEMPL            0 C      070402   .·l¥              0
   DSNHMDB  DSNHMTS            0 C      070402   .¯%.              0
   DSNHMDB  DSNHMTS2           0 C      070402   .¯¡.              0
 Command ===>                                                  Scroll ===> PAGE
  F1=HELP      F2=SPLIT     F3=END       F4=RETURN    F5=RFIND     F6=RCHANGE
  F7=UP        F8=DOWN      F9=SWAP     F10=LEFT     F11=RIGHT    F12=RETRIEVE

Categories: DB2