DB2 SQL error:sqlcode=-1585, sqlstate=54048

DB2 Execute SQL Error: DB2 SQL error:sqlcode=-1585, sqlstate=54048

The DB2 database you built doesn't have a temporary tablespace large enough to create a temporary table space large enough

1. Create a database

DB2 "CREATE DATABASE TestDB automatic storage Yes On/dbauto DBPath on/database using codeset UTF-8 territory CN collate Using System "

DBPath on indicates database directory using CodeSet XXXX terriory XXX Specify database encoding set and zone

1.1 Displaying the database created under the instance

DB2 List DB Directory

2. Database connection

DB2 Connect to TestDB

3. Create a buffer pool

DB2 "Create Bufferpool bp32k size 10000 pagesize 32k" size means number of pages, pagesize represents page size, Size*pagesize is the memory size of the buffer pool

4. Create a data table space for database management (DMS)

DB2 "Create large tablespace tbs_data pagesize 32k managed by database using (file '/data1/tbs_data/cont0 ' 100M, file '/da Ta1/tbs_data/cont1 ' 100M) extentsize prefetchsize automatic bufferpool bp32k no file system caching "

5. Create a temporary tablespace for system Management (SMS)

DB2 "Create temporary tablespace tbs_temp pagesize 32k mamaged by System using ('/data1/tbs_temp ') bufferpool bp32k"

Using the container that specifies the tablespace, the type of container that SMS supports is just the directory

6. Create a temporary tablespace for (SMS) managed users

DB2 "Create user temporary tablespace tbs_user_temp pagesize 32k managed by system using ('/data1/tbs_usertemp ') Bufferpoo L bp32k "

7. Create an automatic storage management (Automatic Storage) tablespace

7.1 DB2 "Create tablespace tbs_index pagesize 32k bufferpool bp32k"

7.2 DB2 "Create tablespace tbs_data2 initialsize 100M increasesize 100M maxsize 1000G"

Table spaces support automatic storage management only if automatic storage Yes is enabled when you build a library

8. Display core information for each tablespace

DB2 List tablespaces

8.1 Displaying information about the specified table space

DB2 list tablespace containers for <tablespace_id> Show Detail

8.2 More detailed display of tablespace information than list tablespaces

DB2 get snapshot for tablespaces on <db_name>

9. Display the table space configuration information, usage and container information

db2pd-d <db_name> tablespaces

10. Changes to the Tablespace container

10.1alter tablespace <db_name> add/drop/extend/reduce/resize

Add and drop operations, the tablespace will have data rebalancing (rebalance), and for the reduce and resize operations, you need to ensure that the changed Tablespace container has enough space, otherwise DB2 will reject the operation.

10.2alter tablespace <db_name> begin new Stripe set

The Begin new stripe set option is to use the newly added container when an existing container is used, which does not rebalance between the containers and does not have a performance impact on the system, but it can cause data offsets.

How to use: DB2 "DB2 alter TABLESPACE DATA_TS2 Add (file '/data1/ts2/cont2 ' 50G)"

10.3DB2 ALTER DATABASE db_name add storage on Db_path3

For automatic storage-managed tablespaces, container changes cannot be made at the table space level, only at the database level, because the automatic storage path is specified when the library is being built. You can add a new storage path for the database using the Add storage on option.

Build Library Script:

DB2 installation default user:
db2inst1
db2iadm1

The first step is to create a new user, add it to the DB2 user, and switch to the new user.
-------------------------------------------------- --------------------------------
[[email protected] ~] # useradd -g db2iadm1 -u 1017 -d / usr / slsadmin -m slsadmin
[[email protected] ~] # passwd slsadmin
[[email protected] ~] # #### slsadmin789

[[email protected] ~] # cd /opt/ibm/db2/V10.5/instance/
[[email protected] instance] # ./db2icrt -u slsadmin slsadmin
[[email protected] instance] # su-slsadmin
-------------------------------------------------- ----------------------------------
Set the environment configuration to allow remote TCP / IP access. Here the number is the port number, you need to use vi to find the smallest port number of this user in etc / services
-------------------------------------------------- ----------------------------------
[[email protected] ~] $ db2 update dbm cfg using SVCENAME 60012
[[email protected] ~] $ db2set DB2COMM = TCPIP
[[email protected] ~] $ db2
-------------------------------------------------- --------------------------------
Start the db2 command window
-------------------------------------------------- --------------------------------
db2 => db2start
db2 => create db faldb using codeset GBK territory CN
db2 => list db directory
db2 => connect to faldb

--db2 => CREATE SCHEMA "hbreport" AUTHORIZATION "hbreport"

--size indicates the number of pages, pagesize indicates the page size, and size * pagesize is the memory size of the buffer pool
db2 => create bufferpool poolname immediate size 10000 pagesize 32k

--Create a database management (DMS) data table space
db2 => create tablespace faldb pagesize 32k managed by system using (‘/usr/slsadmin/NODE0000/sqlt0003.0‘) bufferpool poolname
db2 => create temporary tablespace faldb_temp pagesize 32k managed by system using (‘/ usr / slsadmin / NODE0000 / faldb_temp’) bufferpool poolname

db2 => commit work
db2 => connect reset
db2 => terminate

Execute database build SQL statement
-------------------------------------------------- --------------------------------
[[email protected] ~] $ db2 -tvf /home/WGJ/faldb20160215.sql
[[email protected] ~] $ db2move faldb import -u slsadmin -p slsadmin789


Be sure to turn off the firewall!

Export database
-------------------------------------------------- --------------------------------
[[email protected] ~] $ db2look -d faldb -e -a -x -i slsadmin -w slsadmin789 -0 faldb20160215.sql #Export database structure
[[email protected] ~] $ db2move faldb export -u slsadmin -p slsadmin789 #Export database data 

DB2 SQL error:sqlcode=-1585, sqlstate=54048