Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

DB2 Survival Guide DB2 Environment Basics Creating a Database and Tables (v6.0

DB2 Survival Guide DB2 Environment Basics Creating a Database and Tables (v6.0 & v7.0) Topic Description Create a Database  Using "Command Line Processor" to Create a Database o Start, Programs, IBM DB2, Command Line Processor o db2 => create database <database name> Ex: create database test o db2 => quit  Using "Command Center" to Create a Database o Start, Programs, IBM DB2, Command Center o Click on the "Interactive" tab. o type: create database LS5 o Click on the "Gears" icon. Wait for the message "CREATE DATABASE command completed successfully."  Using "Control Center" to Create a Database o Start, Program Files, DB2 for Win NT, Control Center. o Now that you are in Control Center, click down the tree by choosing: <computer>, Instances, DB2, Databases. o Right click, then click on "Create", "Database using Smart Guide". You can choose the defaults from there. o Some fields you'll need to fill out:  "New Database Name" (only 8 characters),  Alias (can leave blank)  Comment - anything you want.  Other Screens  You can accept all of the defaults by just clicking on "Next" or "Finish" Create a Table in a Database  Create a system user which will be used to logon to the database & it's tables. Now login as that user. Now when you create the TABLE it will assign the user name as the SCHEMA name. If you don't specify the schema DB2 will use the name of the user as the SCHEMA prefix to the table name. Ex: THOMASMA.TEST - <schema name>.<table name>  Using "Command Line Processor" to Create a Table o create table test <... rest of SQL code ....>  Using "Control Center" to Create a Database o Start, Program Files, DB2 for Win NT, Control Center. o Now that you are in Control Center, click down the tree by choosing: <computer>, Instances, DB2, Databases. Then click on the Database name. o Right click and choose: "Create using Wizard". You can choose the defaults from there. o Some fields you'll need to fill out:  Table schema: leave blank to default to the UserID you are logged in as.  Table name: (8 characters)  Next create the fields, primary key, etc... o SQL example:  connect to SAMPLE  create table TEST ( "USERID" character (8) not null, "FIRST" character (15), "LAST" character(20), primary key (USERID) )  Example SQL Code: drop table test; create table TEST ( "USERID" character (8) not null, "FIRST" character (15), "LAST" character(20), primary key (USERID) ); insert into Test (userid, first, last) values ('1','Michael','Thomas'); insert into Test (userid, first, last) values ('2', 'Adam', 'Thomas'); insert into Test (userid, first, last) values ('3', 'Stephen', 'Thomas'); insert into Test (userid, first, last) values ('4', 'Jane', 'Doe'); insert into Test (userid, first, last) values ('5', 'John', 'Doe'); select userid, first, last, concat(first, concat(' ',last)) from Test with UR; drop view v_test1; CREATE VIEW v_test1 ( userid, first, last, fullname ) AS (select userid, first, last, concat(first, concat(' ',last)) from Test); Using: DB2 Command Center Launch: Start, Programs, IBM DB2, Command Center. Topic Description Importing external Scripts Import and run an external Script  Launch the "Script Center": Tools, Script Center  Import a .sql script file. o I've found that DB2 does not like carriage returns or line feeds. Make your commands be on one long line or use "\" for multiple lines.  Fill out the prompted information.  Select the script. Click on "Selected", then "Run Now"  You'll need to launch the "Journal" to see if the script ran successfully. Notes:  The script is imported into DB2 not linked to, therefore changes made to the script's physical file after the import will not change the imported script. Connect to a DB SQL: (Connect to a Database).  connect to <dbname> user <username> using <userpswd> o ex: connect to TEST user db2admin using db2admin View a tables structure SQL:  Connect to the DB first.  describe table <schema>.<tablename> Interactive Tab vs Script Tab Interactive Tab - only allows one SQL command. Script Tab - You can place many SQL commands and highlight the ones you want to run. I like to use the Script Tab. Using: DB2 Control Center Launch: Start, Programs, IBM DB2, Command Center. Topic Description Privileges, Authorities and Access Rights Grant Access to a Database  Launch "Control Center"  <MachineName>, Instances, DB2, Databases. Next right click on the DB and choose "Authorities". Grant access to a Table  Launch "Control Center"  <MachineName>, Instances, DB2, Databases, <db name>, tables. Next right click on the Table. Choose "Privileges". View Sample Data from a Table Steps:  Go to the Tables View (Ex: Systems, <MachineName>, Instances, DB2, Databases, <DatabaseName>, Tables.  Right Click on the Table you want to view Sample contents.  Choose "Sample Contents" o Shows the first 50 records. Press next for the next 50. o "Filter" - click on "Filter" to run a basic query and filter out records. View and Modify a Table Structure Steps:  Go to the Tables View (Ex: Systems, <MachineName>, Instances,DB2, Databases, <DatabaseName>, Tables.  Double click on the Table you want to View/Modify. (Note: You can also right click on the Table and choose "Alter".)  There is a Table for the following: o Table o Columns - shows the structure of the table. o Primary Key o Foreign Keys o Check Constraints  Note: There is a "Show Sql" tab that will show you the SQL that DB2 will perform to make the changes - really neat! Copy a Database Steps:  Optional: If the Database is in use when you go to backup, you may need to Stop and Start DB2: Right click on "DB2" (Ex: Systems, <MachineName>, Instances, DB2) Click Stop. Next, Click Start  Right click on the DB Name: Choose "Backup", "Database". Now complete the information on the screens and then click "Backup Now". o Note: Directory must already exist to where you want to backup up to. o To view the status and output of the job, use the Jobs page on the Journal (Tools, Journal). Or you can wait for the second message to pop up that tells you if the job completed successfully. If you get a backup error you need to go to the Journal to see why.  To Restore a DB from a backup. Right click on the DB Name: Choose "Restore", "Database". Note: All connections to the DB must be released. You can use the following SQL: connect reset force application all Also, you may need to close "Control Center" and launch it again to release the connection.  To Restore to a new DB name from a backup! Right click on the DB Name: Choose "Restore to New" Note: If you get error, you may want to exit "Control Center" and launch it again prior to the below steps. o First, click on the tab "Backup Image" and select the backup file. o Second, click on the tab "Description" and specify the "new database name". Enter the new database name.  "Show SQL" - This button will show you the SQL code for the Backup and Restore operation it will run. Using: Command Line Processor Launch: Start, Programs, IBM DB2, Command Center. Topic Description Command help Command Help  ? <enter> - List commands  ? backup command - List help on a specific command. Backup and Restore or Copy a DB. Backup and Restore (also Copy a DB).  Create a directory to backup to: Ex: C:\db2\mybackup  Backup your DB: Ex: db2 => backup database SAMPLE TO c:\db2\mybackup  Restore the DB: db2 => restore database SAMPLE from C:\db2\mybackup  Restore the DB to another name (ie: Copy DB): db2 => restore database SAMPLE from C:\db2\mybackup into MYSAMPLE Application Heap Size If you're getting Heap Size errors, the double the size of the heap. db2 =>  get dbm cfg - Displays the current aslheapsz:  update dbm cfg using aslheapsz 30 - Sets the heap size to 30. Run SQL Commands db2=> (examples)  list database directory - lists databases on DB2 server  connect to sample user db2admin using db2admin - connects to a db.  list tables - lists tables in the DB.  describe select * from emp_act - lists the structure of a table.  select * from EMP_ACT - list the contents in the table. Help Help db2 => ? - this will give a list of commands. db2 => ? connect to - this will give help on the "connect to" command. Using: Command Window Topic Description Command Window Initializes the DB2 Command Line environment and then takes you to the directory: C:\PROGRAM~1\SQLLIB\BIN Uses:  You can run DB2 exe programs.  You can run DB2 SQL commands. (Examples) o db2 list database directory - lists databases on DB2 server o db2 - connects to a db. o db2 list tables - lists tables in the uploads/Industriel/ db2-survival-guide.pdf