{"id":455,"date":"2009-02-13T11:23:21","date_gmt":"2009-02-13T16:23:21","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=455"},"modified":"2012-10-11T07:34:20","modified_gmt":"2012-10-11T12:34:20","slug":"db2-getting-started","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=455","title":{"rendered":"Db2 Getting Started"},"content":{"rendered":"
\n

Contents<\/h2>\n
    \n\t
  1. \n\t\tDB2 Instance<\/a>\n\t<\/li>\n\t
  2. \n\t\tEnv variables<\/a>\n\t\t
      \n\t\t\t
    1. \n\t\t\t\tUpdate Parameters<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
    2. \n\t\tDatabase<\/a>\n\t\t
        \n\t\t\t
      1. \n\t\t\t\tConnect<\/a>\n\t\t\t<\/li>\n\t\t\t
      2. \n\t\t\t\tDatabases<\/a>\n\t\t\t<\/li>\n\t\t\t
      3. \n\t\t\t\tTablespace<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
      4. \n\t\tExample<\/a>\n\t\t
          \n\t\t\t
        1. \n\t\t\t\tCLP<\/a>\n\t\t\t<\/li>\n\t\t\t
        2. \n\t\t\t\tHelp<\/a>\n\t\t\t<\/li>\n\t\t\t
        3. \n\t\t\t\tScripting<\/a>\n\t\t\t<\/li>\n\t\t<\/ol>\n\t
        4. \n\t\tReferences<\/a>\n\t<\/li>\n<\/ol>\n<\/ol>\n<\/div>\n
           <\/div>\n

          DB2 Instance<\/h2><\/span>\n
          \r\n# Create an instance:\r\ndb2icrt db2inst1\r\n\r\n# Drop an instance:\r\ndb2idrop db2inst1\r\n\r\n# List all instances:\r\ndb2ilist\r\n\r\n# Start an instance:\r\ndb2start\r\n\r\n# Stop an instance:\r\ndb2stop\r\n\r\n# Restart an instance\r\ndb2 force applications all\r\nsleep 10\r\ndb2 stop\r\nsleep 10\r\nps -ef|grep db2inst1\r\ndb2 start\r\n<\/pre>\n
          Env variables<\/h2><\/span>\n
          * Source env variables:<\/p>\n
          \r\n\/home\/db2inst1\/sqllib\/db2profile # source db2cshrc from c shell instead.\r\n<\/pre>\n
          * DB2INSTANCE<\/p>\n
          \r\necho $DB2INSTANCE\r\ndb2inst1\r\n<\/pre>\n
          * db2 profile registry
          \n-global registry
          \n-instance registry<\/p>\n
          \r\n# View all registry\r\ndb2set -lr\r\n\r\n# Set global env\r\ndb2set DB2INSTPROF= -g\r\n\r\n# Set instance env\r\ndb2set DB2INSTPROF= -i MyInst\r\n<\/pre>\n
          * dbm cfg (database mgr cfg file)<\/p>\n
          \r\n# get dbm cfg\r\ndb2 get dbm cfg\r\ndb2 qet db cfg for \r\n\r\n# update dbm cfg\r\ndb2 update dbm cfg using  \r\ndb2 update db cfg for  using  \r\n<\/pre>\n
          Update Parameters<\/h3><\/span>\n
          \r\ndb2 update db cfg for itimdb using database_memory 40000\r\ndb2 alter bufferpool IBMDEFAULTBP size automatic\r\n\r\ndb2set DB2COMM\r\n<\/pre>\n
          Database<\/h2><\/span>\n
          Connect<\/h3><\/span>\n
          * System db directory
          \nContains all dbs you can connect to from your db2 system. Stored at instance level.<\/p>\n
          \r\ndb2 list db directory\r\n<\/pre>\n
          Local: indirect
          \nRemote: remote
          \n* Add db directory entry<\/p>\n
          \r\ndb2 catalog db  as  at node \r\n<\/pre>\n
          * Local db directory
          \nContains info about local dbs.<\/p>\n
          \r\ndb2 list db directory on \r\n<\/pre>\n
          * Node directory
          \nContains all connectivity info for remote databases. Supports only tcpip in ver8.<\/p>\n
          \r\ndb2 list node directory\r\ndb2 catalog tcpip node mynode remote 9.26.138.35 server 60000\r\n<\/pre>\n
          * DCS directory
          \nContains connectivity info for host dbs on zSeries or iSeries machines.<\/p>\n
          \r\ndb2 list dcs directory\r\ndb2 catalog dcs db as db1g\r\n<\/pre>\n
          Databases<\/h3><\/span>\n
          * Create database<\/p>\n
          \r\ndb2 create database temporal\r\ndb2 connect to mydb2 user peenv7\r\ndb2 list tables\r\ndb2 list tablespaces\r\n<\/pre>\n
          Tablespace<\/h3><\/span>\n
          \u2022 Catalog (SYSCATSPACE)
          \n\u2022 Temp (TEMPSPACE1)
          \n\u2022 User space (USERSPACE1)<\/p>\n
          Example<\/h2><\/span>\n
          \r\ndb2ilist\r\ndb2 get instance\r\nset DB2INSTANCE=MyInstance\r\ndb2 list db directory\r\ndb2 create database temporal\r\ndb2 connect to mydb2 user peenv7\r\ndb2 list tables\r\ndb2 list tablespaces\r\n\/\/ backup instance\r\ndb2 qet dbm cfq > dbmcfq.bak\r\ndb2set -all > db2set.bak\r\ndb2 list db directory > systemdbdir.bak\r\ndb2 list node directory > nodedir.bak\r\ndb2 list dcs directory > dcs.bak\r\n\/\/end backup\r\ndb2 drop database temporal\r\n<\/pre>\n
          CLP<\/h3><\/span>\n
          * Verify installation<\/p>\n
          \r\nselect count(*) from syscat.tables\r\nselect count(*) from sysibm.tables\r\nselect count(*) from sysibm.sysdummy1\r\ndescribe table syscat.tables\r\n<\/pre>\n
          Help<\/h3><\/span>\n
          db2 ? sql0100 | more<\/p>\n
          Scripting<\/h3><\/span>\n
          \r\nselect text from syscat.views\r\nselect text,tabname from syscat.checks\r\nselect text from syscat.procedures\r\nselect text from syscat.triggers\r\n<\/pre>\n
          References<\/h2><\/span>\n
          * DB2 HOW TOs<\/a>
          \n*           DB2 cheat sheet<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
          DB2 Instance # Create an instance: db2icrt db2inst1 # Drop an instance: db2idrop db2inst1 # List all instances: db2ilist # Start an instance: db2start # Stop an instance: db2stop # Restart an instance db2 force applications all sleep 10 db2 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[46],"tags":[],"class_list":["post-455","post","type-post","status-publish","format-standard","hentry","category-db2"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-7l","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/455","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=455"}],"version-history":[{"count":5,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/455\/revisions"}],"predecessor-version":[{"id":6236,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/455\/revisions\/6236"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=455"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=455"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=455"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}