DDL (Data Definition Language) statements:

• Define, change, or drop data
• Common DDL:
  • CREATE
  • ALTER
  • TRUNCATE
  • DROP

DML (Data Manipulation Language) statements:

• Read and modify data
• CRUD operations (Create, Read, Update & Delete rows)
• Common DML:
  • INSERT
  • SELECT
  • UPDATE
  • DELETE

Many Relational Database Management Systems (RDBMS) have schemas which contain tables, views, functions, and other database objects.

CREATE TABLE table_name
  (
    column_name_1 datatype optional_parameters,
    column_name_2 datatype,
    ...
    column_name_n datatype
  )

# ALTER
ALTER TABLE <table_name>
  ADD COLUMN <column_name_1> datatype
  ...
  ADD COLUMN <column_name_n> datatype;

# DROP
DROP TABLE <table_name>;

# TRUNCATE
TRUNCATE TABLE <table_name>
  IMMEDIATE;

• Initially populate the entire database
• Create a development and testing copy
• Create a snapshot for disaster recovery
• Create new table using data from external source/file
• Add or append data in existing table

• Backup creates a file for the entire database
• Restore creates exact copy of the database from file
• Preserves all database objects and their data:
  • Schema, Tables, Views
  • User Defined Types, Functions, Stored Procedures
  • Constraints, Triggers, Security
• Backups performed periodically for disaster recovery
• Create copies of database for development and test

• Import inserts data from a file into a table
• Export saves table data into a file
• Operations available using different interfaces
  • Command Line, API, GUI, Third Party Tools

DEL

• Delimited ASCII, for data exchange among a wide variety of database managers and file managers. Includes CSV.

ASC

• Non-delimited ASCII, for importing or loading data from other applications that create flat text files with aligned column data.

PC/IXF

• PC version of the Integration Exchange Format (IXF)

JSON

• Lately, some databases and third-party tools have also started to support importing and exporting data to and from JSON files.

Db2 Command line:

db2 import from *filename* of *fileformat messages messagesfile* into *table*

db2 export from *filename* of *fileformat messages messagesfile* select * from *table*

• Supports XML, large objects, and user-defined types
• Faster than the import utility
• Doesn't perform as many checks
• Preferred option for loading very large datasets
• Initiate from command line / API / Visual Tool
• Command line syntax

db2 load from *filename* of *fileformat messages messagesfile import_mode*
into *table* copy *yes/no* use *tsm* data buffer *pages*

• Data movement is required for initially populating databases and tables, adding or appending data, and making copies for development test or disaster recovery
• BACKUP and RESTORE utilities are used to create and recover copies of entire databases including all objects like tables, views, constraints and their data
• IMPORT utility enables inserting data into a specific table from different formats such as DEL/CSV, ASC and IXF
• EXPORT utility enables saving data from a specific table into various formats like CSV
• LOAD utilities enable high performance insertion of data into specified tables and useful for large volumes

Hands-on Lab: Create Tables and Load Data in Db2

At this point in the course, you know:

• DDL statements, including CREATE, ALTER, TRUNCATE, and DROP, are used for defining objects like tables in a database.
• DML statements, including INSERT, SELECT, UPDATE, and DELETE, are used for manipulating data in tables.
• Many Relational Database Management Systems (RDBMS) have schemas that contain tables, views, functions, and other database objects.
• Most RDBMS provide a GUI through which you can create and alter the structure of tables.

You can also create and alter tables by using DDL SQL statements:

• CREATE TABLE. Creates entities (tables) in a relational database and sets the attributes (columns) in a table, including the names of columns, the data types of columns, and constraints (for example, the Primary Key.)
• ALTER TABLE. Changes the structure of a table by adding or removing columns, modifying the data type of columns, and adding or removing keys and constraints.
• DROP TABLE. Deletes a table from a database.
• TRUNCATE TABLE. Removes all rows in a table.

There are utilities that help you to manage the movement of data:

• You use the BACKUP and RESTORE utilities to create and recover copies of entire databases, including all objects like tables, views, constraints, and data.
• You use the IMPORT utility to insert data into a specific table from different formats, such as DEL/CSV, ASC and IXF, and the EXPORT utility to save data from a specific table into various formats, such as CSV.
• You can use the LOAD utilities, instead of INSERT statements, to quickly insert large amounts of data a variety of different data sources into tables.
• The Load Data utility is a simple to use interface in the Db2 Web Console.

TOTAL POINTS 5

True or False: Inserting data with INSERT statements is often not practical for large amounts of data.

• True
• False

Correct. The process of inputting the data for each row and then uploading it to the database is not very efficient.

Which of the following pieces of information should you gather before you create a table?

• Whether to allow duplicate values in each column
• Whether to allow null values in each column
• The table name
• All of the above

Correct. Ensure you have all information needed to create the table, table name, if columns have null values or can contain duplicate values.

Which of the following data movement scenarios is used for disaster recovery?

• Initial populating of entire database
• Create a working copy of the database
• Add or append data
• Create a snapshot of the database state

Correct. Creating snapshot of database state at particular instant in time for disaster recovery.

Using the CREATE TABLE statement, what is required following the statement?

• Entities
• Table_name
• Primary key
• Datatypes

Correct. The table name must follow the CREATE TABLE statement.

Which of the following common DDL statements can modify data types?

• CREATE
• TRUNCATE
• ALTER
• DROP

Correct. This statement changes tables including adding and dropping columns and modifying data types.

LATEST SUBMISSION GRADE 100%

Which of the following four steps for loading data with the Load Data utility involves authenticating to the storage?

• Target
• Source
• Finalize
• Define

Correct. Identify type and location of source data enter any authentication requirements for that storage type.

You create a new table in the COR38310 schema and name it Engineers. What is the fully qualified name for this table?

• Engineers.COR38310
• COR38310/Engineers
• Engineers
• COR38310.Engineers

Correct. Fully qualified table names have the schema before the table name separated by a dot.

Multiple file formats are supported by most databases. Which of the following common formats includes CSV files?

• JavaScript Object Notation (JSON)
• Non-delimited ASCII (ASC)
• Delimited ASCII (DEL)
• PC Integration exchange (PC/IXF)

Correct. A comma separated variable (CSV) file is a form of delimited ASCII file.

True or False - The Primary Key uniquely identifies each row in a table.

• True
• False

Correct. The Primary Key uniquely identifies each row in a table. It is often implemented as an ID number, or a unique code.

Question 5

What are DML statements sometimes referred to as?

• CRUD
• UPDATE
• Modifier
• CREATE

Correct. DML statements are sometimes referred to as Create, Read, Update, and Delete.