How to create a database

[xing165]

Hi
i want to create a database,then create a table.But I fail.How to create a database.thanks you.
this is the error code

Code: Select all

Command ===>                                                                 
Rollback done                                                                 
   SQLCODE : -904                           DSNTIAR CODE :  0                 
                                                                             
DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN         
         UNAVAILABLE RESOURCE. REASON 00C90081, TYPE OF RESOURCE 00000201, AND
         RESOURCE NAME DSNDB06 .DSNADH01                                     
DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE                             
DSNT415I SQLERRP    = DSNXA06 SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 355  13172746  0  13223106  -974970878  12714050 SQL   
         DIAGNOSTIC INFORMATION                                               
DSNT416I SQLERRD    = X'00000163'  X'00C9000A'  X'00000000'  X'00C9C4C2'     
         X'C5E32002'  X'00C20042' SQL DIAGNOSTIC INFORMATION

[nclouston]

Well, I am definitely not a DB2 admin guru - in fact a cow possibly knows as much as me but...my understanding is that you create a storage group(?) first, then your DB then your tables. I do not even know how to go about taking the first step so do not ask me!

[prino]

FanDeZhi is a system for people who know what to do and how to do it. It is not a system for obvious clueless newbies like you. I suggest you start reading manuals for the next few weeks.

You are telling us "But I fail."

Fail with what, failing to read manuals? Failing to tell us what error messages you get?

[rsilver]

I agree ...DBs are created by DBAs, not users. If you type command QMF, you will see the database is DALLAS8 and you should be able to create a table in this DB.
z/OS (mainframe) is not like Oracle, SQL Server, or whatever...read the manual and learn what a mainframe is about before you post a question that makes no sense !!!

[nclouston]

Unfortunately there are no DBAs here so you have to 'roll your own' so I fall at the first hurdle not knowing how (easy to read up on though) or where (nothing on site to tell us) to create our little bundles of joy!

[rsilver]

Unfortunately there are no DBAs here so you have to 'roll your own' so I fall at the first hurdle not knowing how (easy to read up on though) or where (nothing on site to tell us) to create our little bundles of joy!

There are DB2 forums were you can ask questions but you have to have a basic understanding of how the mainframe works and how to use the DB2 tools SPUFI (SQL Processing Using File Input) or QMF(Query Management Facility) and you also have to know basic SQL DML and DDL. DML (SELECT, UPDATE, INSERT, ETC) DDL(CREATE, ALTER, DROP, ETC).

If you want to see a COBOL example, see one I wrote, COBOL with imbedded SQL using DCLGENS created by DB2I, option 2. http://home.mebtel.net/~rsilvers/Rexx_work/FBB1716.txt

[nclouston]

I've been using DB2/SQL on and off since 1988 or so but only as a programmer, not as a DBA. Most places do not allow you to put your stuff on any-old dasd. Here I do not even know what dasd is available, where I should put my stuff (DB2, IMS, CICS etc). Until I figure out some of the DBA stuff I am not going to start - unlike some people who don't give a toss about other users of the system. Like I said before, I can read up on DB2 etc but I cannot read up on FanDezhi so I need to poke around without disturbing other folks.

[rsilver]

Yep me too, in a different world now at BOA...working with Teradata, Oracle/Unix, and Netezza/Linux...use Fandezhi to keep my DB2 skills current...trying to become proficient in Unix and Linux scripting and Teradata language

[gary]

For those of you who apparently believe you know everything, I would be more useful for you to post a tutorial, rather than just say "If you don't know, you shouldn't be here.".

I too have had problems creating a database, using "Create Database Name;". Apparently because parts of DB2 are not started. I don't have a DBA to create it for me, and I don't have permission to start what is needed.

So, simply telling me that I don't know what I'm doing is no help at all. If you want to help, feel free. If not, then mind your own business.

DB2 Admin ----------- Execute SQL Statements from Screen Input ---------- 14:41
Command ===>

DB2 System: DB8G
SQL statement: DB2 SQL ID: LP187
create database lp187tst;

Generates:
DB2 Admin --------------------- DB2 Error Display 1 --------------------- 14:43
Command ===>
Rollback done
SQLCODE : -904 DSNTIAR CODE : 0

DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN
UNAVAILABLE RESOURCE. REASON 00C90081, TYPE OF RESOURCE 00000201, AND
RESOURCE NAME DSNDB06 .DSNADH01
DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXA06 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 355 13172746 0 13223106 -974970878 12714050 SQL
DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000163' X'00C9000A' X'00000000' X'00C9C4C2'
X'C5E32002' X'00C20042' SQL DIAGNOSTIC INFORMATION

DB2 Admin ------------------- DB8G Databases -------------------- Row 1 of 345
Command ===> Scroll ===> CSR

Commands: GRANT
Line commands:
T - Tables S - Table spaces X - Indexes G - Storage group ICS - IC status
DIS - Display database STA - Start database STO - Stop database A - Auth
? - Show all line commands
Storage Buffer Created Index
Select Name Owner Group Pool DBID By T E Buffer Pool
* * * * * * * * *
------ -------- -------- -------- -------- ------ -------- - - -----------
DSNDB04 SYSIBM SYSDEFLT BP0 4 SYSIBM BP0
DSNDB06 SYSIBM 6 SYSIBM E BP0
DSNDB07 IBMUSER SYSDEFLT BP0 7 IBMUSER W BP0

It appears to me that DSNDB06 needs to be started, or some other help.

If you can help, please do. If you just want to tell me I'm stupid, save your breath.

Gary

[monitor]

Right, Gary!!!
If you are told not to ask questions, whats the forum for?

[nclouston]

I beleive this forum is not for 'how do I do this in DB2/COBOL/IMS/REXX/etc' as there are forums for asking these questions. The forum ahould really be for "How do I do this on FanDezhi?" exactly like the sort of questions that could be answered in a 'sticky' e.g what dasd is available for what purpose, how to logon to cics/ims/whatever in this environment, where the compile procs are in this environment. I did do a bit on some of these but it was only from what I learned from browsing the forum - CICS logon and compile procs. But all that went "poof" along with my hard-drive (no backup of that particular file ) Now I do not have the time to reconstruct it but I will try to over the coming weeks/months and if someone can make it a sticky it will be a benefit to all users. Or maybe somone has this already?

[gary]

I think there is some confusion here. Most of the posts I see are not asking "How do you create a database in DB2". That's pretty simple, you execute the "CREATE DATABASE" DDL statement. The question that I and others have been asking is "why does the CREATE DATABASE statement fail". This can only be asked here, since it only fails on this system.

Gary

[prino]

DB2 has been sick for months, sysprog has recovered it several times, only to see it being screwed up again within a very short period of time. His time is limited and the other admins lack enough DB2 knowledge to get things back onto the rails, so DB2 is for all intents and purposes not available, tough, but that's life...

[rsilver]

For those of you who apparently believe you know everything, I would be more useful for you to post a tutorial, rather than just say "If you don't know, you shouldn't be here.".

I too have had problems creating a database, using "Create Database Name;". Apparently because parts of DB2 are not started. I don't have a DBA to create it for me, and I don't have permission to start what is needed.

So, simply telling me that I don't know what I'm doing is no help at all. If you want to help, feel free. If not, then mind your own business.

DB2 Admin ----------- Execute SQL Statements from Screen Input ---------- 14:41
Command ===>

DB2 System: DB8G
SQL statement: DB2 SQL ID: LP187
create database lp187tst;

Generates:
DB2 Admin --------------------- DB2 Error Display 1 --------------------- 14:43
Command ===>
Rollback done
SQLCODE : -904 DSNTIAR CODE : 0

DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN
UNAVAILABLE RESOURCE. REASON 00C90081, TYPE OF RESOURCE 00000201, AND
RESOURCE NAME DSNDB06 .DSNADH01
DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXA06 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 355 13172746 0 13223106 -974970878 12714050 SQL
DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000163' X'00C9000A' X'00000000' X'00C9C4C2'
X'C5E32002' X'00C20042' SQL DIAGNOSTIC INFORMATION

DB2 Admin ------------------- DB8G Databases -------------------- Row 1 of 345
Command ===> Scroll ===> CSR

Commands: GRANT
Line commands:
T - Tables S - Table spaces X - Indexes G - Storage group ICS - IC status
DIS - Display database STA - Start database STO - Stop database A - Auth
? - Show all line commands
Storage Buffer Created Index
Select Name Owner Group Pool DBID By T E Buffer Pool
* * * * * * * * *
------ -------- -------- -------- -------- ------ -------- - - -----------
DSNDB04 SYSIBM SYSDEFLT BP0 4 SYSIBM BP0
DSNDB06 SYSIBM 6 SYSIBM E BP0
DSNDB07 IBMUSER SYSDEFLT BP0 7 IBMUSER W BP0

It appears to me that DSNDB06 needs to be started, or some other help.

If you can help, please do. If you just want to tell me I'm stupid, save your breath.

Gary

suggestion ... Fandezhi has limited (what I have seen) resources...if you want to create databases and all the rest . . .download the "FREE" version of DB2 Express http://www-01.ibm.com/software/data/db2/express/
and you can do what ever you want ....DB2 is DB2 whatever platform/OS it is running on...

[kwcaldw]

No objects can be created in DB2 because of the unavailable resource 'DSNDB06 .DSNADH01'.
The reason code (00C90081) indicates the resource (00000201 - an indexspace) is stopped.

An attempt to start the object results in an authorization error:

-START DATABASE(DSNDB06) SPACENAM(DSNADH01)

DSNT300I -DB8G AUTH-ID KWCALDW NOT AUTHORIZED TO PERFORM START ON
DATA BASE DSNDB06. REQUEST REJECTED
DSNT500I -DB8G DSNTDMI RESOURCE UNAVAILABLE
REASON 00C90081
TYPE 00000201
NAME DSNDB06 .DSNADH01
DSN9023I -DB8G DSNTDDIS 'START DATABASE' ABNORMAL COMPLETION

Since SYSIBM is the creator of this object, presumably only they can start it.

*****
I agree with rsilver - if you can't say something nice (or provide a constructive answer) don't say anything at all.
*****

Perhaps, also, all "user" databases should be dropped and start over to clean/free some resources.