Your cart is currently empty!
Category: db2
-
Khóa học miễn phí DB2 with XML nhận dự án làm có lương
DB2 – with XML
This chapter describes use of XML with DB2.
Introduction
PureXML feature allows you to store well-formed XML documents in columns of database tables. Those columns have XML database. Data is kept in its native hierarchical form by storing XML data in XML column. The stored XML data can be accessed and managed by DB2 database server functionality. The storage of XML data in its native hierarchical form enables efficient search, retrieval, and update of XML. To update a value in XML data, you need to use XQuery, SQL or combination of both.
Creating a database and table for storing XML data
Create a database by issuing the following syntax:
Syntax:
db2 create database xmldb
By default, databases use UTF-8 (UNICODE) code set. Activate the database and connect to it:
Syntax:
db2 activate db <db_name> db2 connect to <db_name>
Example:
db2 activate db xmldb db2 connect to xmldb
Create a well-formed XML file and create a table with data type of the column as ‘XML’. It is mandatory to pass the SQL query containing XML syntax within double quotation marks.
Syntax:
db2 “create table <schema>.<table>(col <datatype>, col <xml datatype>)”
Example:
db2 "create table shope.books(id bigint not null primary key, book XML)"
Insert xml values into table, well-formed XML documents are inserted into XML type column using SQL statement ‘INSERT’.
Syntax:
db2 “insert into <table_name> values(value1, value2)”
Example:
db2 "insert into shope.books values(1000, ''<catalog> <book> <author> Gambardella Matthew</author> <title>XML Developers Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> <description>An in-depth look at creating application with XML</description> </book> </catalog>'')"
Updating XML data in a table
You can update XML data in a table by using the following syntax:
Syntax:
db2 “update <table_name> set <column>=<value> where <column>=<value>”
Example:
db2 "update shope.books set book=''<catalog> <book> <author> Gambardella, Matthew</author> <title>XML Developers Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> <description>An in-depth XML</description> </book> </catalog>'' where id=1000"
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
Khóa học miễn phí DB2 – Schemas nhận dự án làm có lương
DB2 – Schemas
This chapter introduces and describes the concept of Schema.
Introduction
A schema is a collection of named objects classified logically in the database.
In a database, you cannot create multiple database objects with same name. To do so, the schema provides a group environment. You can create multiple schemas in a database and you can create multiple database objects with same name, with different schema groups.
A schema can contain tables, functions, indices, tablespaces, procedures, triggers etc. For example, you create two different schemas named as “Professional” and “Personal” for an “employee” database. It is possible to make two different tables with the same name “Employee”. In this environment, one table has professional information and the other has personal information of employee. In spite of having two tables with the same name, they have two different schemas “Personal” and “Professional”. Hence, the user can work with both without encountering any problem. This feature is useful when there are constraints on the naming of tables.
Let us see few commands related to Schema:
Getting currently active schema
Syntax:
db2 get schema
Example: [To get current database schema]
db2 get schema
Setting another schema to current environment
Syntax:
db2 set schema=<schema_name>
Example: [To arrange ‘schema1’ to current instance environment]
db2 set schema=schema1
Creating a new Schema
Syntax: [To create a new schema with authorized user id]
db2 create schema <schema_name> authroization <inst_user>
Example: [To create “schema1” schema authorized with ‘db2inst2”]
db2 create schema schema1 authorization db2inst2
Exercise
Let us create two different tables with same name but two different schemas. Here, you create employee table with two different schemas, one for personal and the other for professional information.
Step 1: Create two schemas.
Schema 1: [To create schema named professional]
db2 create schema professional authorization db2inst2
Schema 2: [To create schema named personal]
db2 create schema personal authorization db2inst2
Step 2: Create two tables with the same name for Employee details
Table1: professional.employee
[To create a new table ‘employee’ in the database using schema name ‘professional’]
db2 create table professional.employee(id number, name varchar(20), profession varchar(20), join_date date, salary number);
Table2: personal.employee
[To create a new table ‘employee’ in the same database, with schema name ‘personal’]
db2 create table personal.employee(id number, name varchar(20), d_birth date, phone bigint, address varchar(200));
After executing these steps, you get two tables with same name ’employee’, with two different schemas.
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
