WIXLIB

Chapter 3: Getting Connected to DB2 UDBIBM DB2 Universal Database V8.1Database Administration Certification Preparation CourseMaintained by Clara Liu

ObjectivesIn this section, we will cover:Setup connectivity to remote databasesUsing discoveryUsing access profilesManuallyInstance Attachment vs Database ConnectionManaging Database Connections

Chapter 3: Getting Connected to DB2 UDBSetup Database ConnectivityInstance Attachment vs Database Connection

DB2 Client / Server EnvironmentApplicationDevelopmentClientRuntime ClientAdministrationClientTCPIP, APPC, NetBIOS, or NPIPEDB2 ServerDB2 Connect for HostConnectivityLocalClientDB2dataUNIX, Windows, or LinuxDB2/MVSDRDADB2/VSE& VMS/390Non-IBMRDBMSDB2 ConnectAS/400DRDADB2 ConnectDB2/400RDBMSDRDA: Distributed Relational Database Architecture

Preparing the Server for DB2 Remote ConnectionsDBA must ensure server side communications is set up properlyStep 1:Enable the instance for communication supports, set DB2 registry variabledb2set DB2COMM TCPIP,NETBIOSStep 2:Set the protocol information in database manager configuration, for TCPIP:db2 update dbm cfg using svcename 50000ORdb2 update dbm cfg using svcename db2icdb2Update /etc/services with the service name and port numberdb2icdb250000/tcpStep 3:db2stop and db2start instance after changes to database manager configuration

DB2 Discovery at DB2 ClientsSearch and locate DB2 servers on the networkTwo methods: Known and SearchRequires DAS running and enabled to be discovered on the DB2 serversCan prevent or "hide" a server / database from being discoveredSearchSearch the networkfor any serverKnownEnter the name of serverand choose the protocol

Discovery ExampleDB2 UDB ClientDiscovery MessageDISCOVER DISABLEdoes not allow themachine to be seenduring a client searchDB2 UDB ServerDB2 Administration Server LevelDISCOVER SEARCHInstance LevelDISCOVER INST DISABLEDISCOVER INST ENABLEDatabase LevelDISCOVER DB DISABLEDISCOVER DB ENABLE

Use of DB2 Configuration Assistant

Use of DB2 Configuration Assistant

Using Access ProfilesAssist with automation of client configurationAccess profiles contain the information that a client needs to catalogdatabases on a DB2 UDB serverTwo types of Access Profiles:Server Access Profile generated on a UDB serverInformation about all instances/databases on the serverClient Access Profile generated on a clientUsed to copy the client's catalog information to a number of other clients

Exporting Access Profile

Example of Access Profile[FILE DESCRIPTION]APPLICATION DB2/NT 8.1.0FILE CONTENT DB2 CCA Exported Data SourcesFILE TYPE CommonServerFILE FORMAT VERSION 2.0Platform 5DB2SYSTEM CLARALIUInstance DB2[REGISTRY GLOBAL]DB2 GRP LOOKUP LOCALDB2INSTDEF DB2[REGISTRY LOCAL]DB2ACCOUNTNAME CLARALIU\db2adminDB2INSTOWNER CLARALIUDB2PORTRANGE 60000:60003DB2 GRP LOOKUP LOCALDB2COMM TCPIP[INST DB2]instance name DB2NodeType 4ServerType DB2NTAuthentication SERVERDB2COMM TCPIP[DB !LOCAL:SAMPLE]Dir entry type INDIRECTDrive C:\DB2DBName SAMPLE

Importing Access Profile

Manual Client/Server Configuration

Configure Client Connectivity via CommandsCatalog NODECatalog each instance on DB2 UDB ServerSpecific command for each supported communication protocolExamples:CATALOG TCPIP NODE mynode REMOTE server1 SERVER 50000CATALOG NETBIOS NODE jeremy REMOTE N01FCBE3 ADAPTER 0Catalog DATABASECatalogs each database on DB2 UDB Server and instanceWorks for local and remote databasesExample:CATALOG DATABASE sample AS mysamp AT NODE mynodeCatalog DCS DATABASEDatabase Connection Service (DCS)Catalogs DRDA databases on OS/390, AS/400, or VM,VSEThese databases are accessed through an Application Requester (AR), such as DB2 ConnectHaving a DCS directory entry with a database name matching a database name in the systemdatabase directory invokes the AR to forward SQL requests to the host serverExample:CATALOG DCS DATABASE db1 AS dsn db 1

Cataloging the remote DASFacilitates remote node (instance) administrationRequired for full remote instance abstractionALWAYS assumes port 523CATALOG ADMIN TCPIP NODE node alias REMOTE hostname Example:CATALOG ADMIN TCPIP NODE zeus REMOTE zeus.mycompany.com

List of CATALOG CommandsCATALOG APPC NODE .APPC - Advanced Program to Program Communications protocolCATALOG APPN NODE .CATALOG DATABASE .CATALOG DCS DATABASE .CATALOG LDAP DATABASE .CATALOG LDAP NODE .CATALOG LOCAL NODE .CATALOG NAMED PIPE NODE .CATALOG ODBC DATA SOURCE .CATALOG TCPIP NODE .

The Node DirectoryLIST NODE DIRECTORY

The Database DirectoryLIST DATABASE DIRECTORY

The Database DirectoryObtain list of local databases created on a drive or mount pointLIST DB DIRECTORY ON c:

DB2 Client ConnectivityAll databases appear LOCAL to applicationsNode and DB directories abstract away real location of databaseEach remote database points to a nodeEach node points to an remote instancePlatform transparentDB2 Client Machinedb directoryapp1hropsapp2app3mkthost2 (nt)node saleshost1 (AIX)operationsdb2inst1marketingsalesdb2inst3

Chapter 3: Getting Connected to DB2 UDBSetup Database ConnectivityInstance Attachment vs Database Connection

Instance Attachment vs Database ConnectionINSTANCE ATTACHMENTcreate/drop databasesget/update/reset database manager and database configuration filedatabase monitorbackup/restore/roll forward databaseforce applicationDATABASE CONNECTIONDML, DDL, DCLprecompile/bind applicationsload/export/import

ATTACH and CONNECT CommandsINSTANCE ATTACHMENTImplicit:DB2INSTANCE instance name Environment VariableExplicit:ATTACH TO nodename [USER . USING.]Nodename is importantFor local instances onlyFor local and remoteinstancesDATABASE CONNECTIONImplicit:DB2 Profile variable,set with db2set commanddb2set DB2DBDFT database name Explicit:CONNECT TO db-alias [USER . USING. CHANGE PASSWORD]

Remote Adminstration - ATTACHAttach to a node that has been cataloged in the DB2 node directoryFor example:ATTACH TO db2instx USER db2admin USING passwdRESTORE DATABASE hr FROM /db2/backup REPLACE EXISTINGDETACHUser ID and password are required if the node is a remote nodeDB2 Client Machinedb directoryapp1hropsapp2app3mkthost2 (nt)node saleshost1 (AIX)operationsdb2inst1marketingsalesdb2inst3

Attach to an instance using DB2 Control Center

Connect to a DatabaseConnect to a database that has been cataloged in the DB2 database directoryExample:CONNECT TO sample USER db2admin USING passwdINSERT INTO testtab VALUES (1, 'DB2')CONNECT RESETUser ID and password are required if database is on a remote serverTo find out currently connected database, use one of the following commands:GET CONNECTION STATEDatabase Connection StateConnection state Connectable and ConnectedConnection mode SHARELocal database alias SAMPLEDatabase name SAMPLEVALUES CURRENT SERVER1-----------------TOOLSDB1 record(s) selected.Special Register

Some Useful CommandsLIST APPLICATIONS [SHOW DETAIL]Shows all current connections made to any databases defined within an instanceAuth Id ApplicationAppl.Application IdDB# ofNameHandleNameAgents-------- ---------------------------- ---------- --------------------------------------------- -------------- ---------CLARALIU db2bp.exe7*LOCAL.DB2.00E000150926SAMPLE1CLARALIU db2bp.exe6*LOCAL.DB2.00F1C0150335SAMPLE1DB2ADMIN db2dasstm.exe 5*LOCAL.DB2.005A40134734TOOLSDB1DB2ADMIN db2dasstm.exe 4*LOCAL.DB2.005A40134733TOOLSDB1FORCE APPLICATION ALLDisconnect all connections made to any databases defined within an instanceFORCE APPLICATION (h1 [,h2,.hn])Selectively disconnect applications by the application handle identifierGET INSTANCEretrieve your current instance contextThe current database manager instance is: DB2

The Database Directory Obtain list of local databases created on a drive or mount point LIST DB DIRECTORY ON c: DB2 Client Connectivity . Connect to a database that has been cataloged in the DB2 database directory Example: CONNECT TO sample USER db2admin USING passwd INSERT INTO testtab VALUES (1, 'DB2')