Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Denver, Colorado USA
    Posts
    2

    Unanswered: Creating new database using SQL

    I am a DB2 newbie, but I've worked a lot with MySQL and quite a bit with Oracle. I want to know if it's possible to create a user and database using straight SQL. The reason I ask is because I currently do this with Ant and MySQL - simply logging in to the "mysql" database and running the following SQL script:

    create database if not exists apptracker;
    grant all privileges on apptracker.* to test@"%" identified by "test";
    grant all privileges on apptracker.* to test@localhost identified by "test";

    This script in mysql creates both the "test" user and the "apptracker" database.

    Secondly, I'd like to know how to adjust the default table space page sizes as part of this script (or when running "create database apptrack" from the Command Line Processor. The reason is because I found some issues today using the default setup. My issues and workarounds can be found on my wiki at http://tinyurl.com/245r2.

    Basically, I had to create a new Buffer Pool with Page Size = 8 and also create an additional Temporary Table Space to use this new Buffer Pool.

    Thanks - any help is appreciated.

    Matt

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can run the create database command in a script, but it is considered a command and not SQL. See the Command Reference manual. Then you can run the SQL to grant access.

    Your url link does not seem to work.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2004
    Location
    Denver, Colorado USA
    Posts
    2
    Originally posted by Marcus_A
    You can run the create database command in a script, but it is considered a command and not SQL. See the Command Reference manual. Then you can run the SQL to grant access.

    Your url link does not seem to work.
    The URL looks like it got a period appended to it, try http://tinyurl.com/245r2

    Here is the ant <sql> task I'm using the create the database for MySQL - I was hoping it would be as simple with DB2. If not, I just want to know that it's not that simple.

    <!-- ================================================== ================= -->
    <!-- The "db-create" target creates a database based on properties -->
    <!-- from the database.properties file -->
    <!-- ================================================== ================= -->

    <target name="db-create" depends="define-tasks"
    description="create database for ${database.type}">
    <echo>Creating database with file: ${database.type}-create.sql</echo>
    <sql
    driver="${hibernate.connection.driver_class}"
    url="${database.name}"
    userid="${hibernate.connection.username}"
    password="${hibernate.connection.password}">

    <classpath refid="hibernate.classpath" />

    <fileset dir="metadata/sql">
    <include name="${database.type}-create.sql"/>
    </fileset>
    </sql>
    </target>

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can create an 8K buffer pool and Tempory and User tablespaces with page size = 8K. I would not change the default if you can help it. DB2 will automatically use the right temporary tablespace (to match the page size) and you can specify the page size when you create a tablspace. Check the SQL Reference for create bufferpool and create tablespace.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •