How to execute sql script file in db2 command prompt

db2 -tvsf <script>.sql

A temporary table could not be created because there is no available system temporary table space that has a compatible page size

com.ibm.db2.jcc.am.SqlException: A temporary table could not be created because there is no available system temporary table space that has a compatible page size.. SQLCODE=-1585, SQLSTATE=54048, DRIVER=4.13.127

Solution - Need to create a system temporary tablespace with page size of 32K.

create bufferpool unicasysbpool size 500 pagesize 32K

CREATE TEMPORARY TABLESPACE TEMPSPACE2 pagesize 32k MANAGED BY
DATABASE USING ((FILE 'D:\DB2\NODE0000\UNICADB\TEMPSPACE2.TBS' 20M)
BUFFERPOOL unicasysbpool

SQL1582N The PAGESIZE of the table space "UNICA" does not match the PAGESIZE of the bufferpool "IBMDEFAULTBP" associated with the table space.

If you get the above error, it means you need to create a new bufferpool with a different page size as you require.

CREATE BUFFERPOOL UNICABPOOL  SIZE 3000 PAGESIZE 16K

Then use this bufferpool:

CREATE REGULAR TABLESPACE UNICA
    PAGESIZE 16K
    MANAGED BY DATABASE
    USING (FILE 'D:\DB2\NODE0000\UNICADB\UNICA.TBS' 80M) 
    BUFFERPOOL UNICABPOOL  AUTORESIZE YES

Prevent use of USERSPACE1

The default tablespace of any users in DB2 is USERSPACE1.

Even if you create a new tablespace and grant a user access to that tablespace, it will use USERSPACE1 first when creating a table (if you did not specify a tablespace).

To prevent the use USERSPACE1 tablespace. revoke the use of this tablespace:
REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC

A table space could not be found with a page size of at least "4096" that authorization ID "COGNOS" is authorized to use

If you get the above error, it means the DB user does not have any privileges to use any tablespace.
See this link : http://www-01.ibm.com/support/docview.wss?uid=swg21339551

Solution Example:
db2 => drop tablespace cognos
DB20000I  The SQL command completed successfully.
db2 => create regular tablespace cognos pagesize 4k managed by database using (file 'D:\DB2\NODE0000\UNICADB\COGNOS.TBS' 5000 ) AUTORESIZE YES
DB20000I  The SQL command completed successfully.
db2 => grant use of cognos to cognos
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "of" was found following "grant use ".  Expected
tokens may include:  "ON".  SQLSTATE=42601
db2 => grant use of tablespace cognos to user cognos
DB20000I  The SQL command completed successfully.
db2 => CREATE USER TEMPORARY TABLESPACE USERTEMP MANAGED BY DATABASE USING (FILE 'D:\DB2\NODE0000\UNICADB\USERTEMP.TBS' 5000 ) AUTORESIZE YES
DB20000I  The SQL command completed successfully.
db2 => grant use of tablespace usertemp to user cognos
DB20000I  The SQL command completed successfully.
db2 =>

Reference Site

This site seems quite good :
 

DB2 Basics: Table spaces and buffer pools

This is a good read:
http://www.ibm.com/developerworks/data/library/techarticle/0212wieser/

Create a new database

db2 => create db unicadb
DB20000I  The CREATE DATABASE command completed successfully.

db2 => get  connection state
   Database Connection State
 Connection state       = Connectable and Unconnected
 Connection mode        =
 Local database alias   =
 Database name          =
 Hostname               =
 Service name           =
db2 => connect to unicadb
   Database Connection Information
 Database server        = DB2/NT64 10.1.0
 SQL authorization ID   = ADMINIST...
 Local database alias   = UNICADB
db2 => list tablespaces
           Tablespaces for Current Database
 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Tablespace ID                        = 3
 Name                                 = SYSTOOLSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal


db2 => create regular tablespace unica01 pagesize 4k managed by database using (
file 'D:\DB2\NODE0000\UNICADB\UNICA01.TBS' 20000 )
DB20000I  The SQL command completed successfully.