NAME

DB2::db - Framework wrapper around DBD::DB2 for a specific database

SYNOPSIS

  package myDB;
  use DB2::db
  our @ISA = qw( DB2::db );

  ...

  use myDB;

  my $db  = myDB->new;
  my $tbl = $db->get_table('myTable');
  my $row = $tbl->find($id);

DESCRIPTION

The DB2::db module can simplify your interaction with a DB2 database using the DBI module. The cost is generally a little bit of speed since it cannot know which columns you may be interested in. This is not always bad since you may not know either.

Please note that unlike many of the DBIx::* modules, this framework is intended to create your tables (and database) as well as manage them. Most DBIx modules will assume your tables are already created and leave the ability to recreate your tables up to you. The design for DB2::db is intended to allow you to develop on one machine and deploy on another with a little less effort. In exchange, however, it can be significantly more work to set up your perl scripts in the first place. That said, the extra work in setting up your perl modules is probably only a little more than the work it would require to create a DDL script to create all your tables.

SETUP

Prior to using your db object, you will need to set $ENV{DB2INSTANCE}. This is so the DB2 driver will be able to figure out your instance. DB2::db defaults to an instance called 'db2ee':

    BEGIN {$ENV{DB2INSTANCE} = 'db2ee' unless $ENV{DB2INSTANCE}};

The default instance for DB2 is "db2inst1" on Unix, and "db2" on Windows. Thus this default is equally wrong everywhere. If you want to change this default outside of a BEGIN block, you must do so before creating your DB2::db object.

FUNCTIONS

Some functions you have to override to get any meaningful use - these are the generics of the framework. Others you may call. Yet others should not be called at all.

new

Do not override this one. This will return a cached version of your database object if there is one. Also known as the singleton approach. If you need to initialise it, you're best off doing so after creation in your own method.

db_name

Override this, returning the database name that will be connected to with this object. Failure to override will result in a crash quickly.

dsn

Override this returning a hash with keys for database, host, and port for constructing the dsn. Useful if the database may not be local.

If you override dsn to just return db => $db_name, this should be equivalent to overriding db_name. This can give more flexibility as to which db to use - allowing you to use a remote db for production, but a local db for development, for example.

user_name

Override this if necessary. The default is no user, which will imply that the current user (however that is defined for your platform) will be the user for authentication purposes. Usually you will need to get this information before creation of your database object.

user_pw

Similar to user_name, override if necessary. Should be overridden if user_name is overridden. Must return the unencrypted password.

connect_attr

This is used for any connection-specific parameters needed for the underlying DBD::DB2 object. The default is to turn off AutoCommit (since this framework handles commits already). Example:

    sub connect_attr {
        my $self = shift;
        my %attr = (
                    %{$self->SUPER::connect_attr()}, 
                    LongReadLen => 102400,
                   );
        \%attr;
    }

setup_row_table_relationships

Override this to tell DB2::db about your tables. Call add_row_table_relationship once for each table (see its documentation below).

Order is important. The order will be preserved and used when attempting to create the database. Ensure the tables are listed in such an order that FOREIGNKEYs always point to tables that will be created before the current table.

add_row_table_relationship

While initialising the row/table relationships, call this in the order of the tables that would need to be created.

    $self->add_row_table_relationship(
                                      ROW => 'MyRow',
                                      TABLE => 'MyTable',
                                      %other_options
                                     )

Do this once for each table you have.

Note that if ROW is missing, it will be assumed to be the same as Table, but with an R suffix. e.g., $self->add_row_table_relationship(TABLE => 'tbl') will assume that the Row's object type is tblR

Other options include:

IS_FULL_PKG_NAME

If this is true, it is assumed that you have fully qualified your package names for both the row and the table. Otherwise, the default is to use the same package as your database object is in. For example, if your object is in the package My (e.g., My::db), then specifying ROW => 'MyRow' implies My::MyRow instead. This can save a bunch of typing if you have a deeply-nested package tree, or if you decide to change the package later.

ROW_IS_FULL_PKG_NAME

TABLE_IS_FULL_PKG_NAME

Specific to the ROW and TABLE, respectively.

add_table

Same as add_row_table_relationship, except that the first parameter is the table name, and the rest are options. For example,

    $self->add_table("tbl", ROW => "tbl::row");

is exactly the same as:

    $self->add_row_table_relationship(TABLE => "tbl", ROW => "tbl::row");

Which means that if you follow conventions, you only need to specify:

    $self->add_table("tbl");

if your row package is tblR. Order is still important. add_table and add_row_table_relationship can be intermingled.

add_tables

And, finally, a shortcut to calling add_table repeatedly if you're just using the defaults anyway.

    $self->add_tables(qw/
                      tbl1
                      tbl2
                      /);

get_row_table_relationships

OBSOLETE - Use setup_row_table_relationships instead.

Override this with the DB2::Table/DB2::Row relationships. This will be used to extrapolate what objects to create for each query. DB2::Table objects will be instantiated as required, but no sooner.

Format of expected output: [ { ROW => 'Row_type_1', TABLE => 'Table_type_1' }, { ROW => 'Row_type_2', TABLE => 'Table_type_2' }, ]

The order of these hashrefs is important. The order is used in determining what order to create the tables during table creation in create_db.

Types mean package names. "Classes" for you Java and C++ types out there. When DB2::db needs to create a row object to handle the data retrieved from the database table, it will look up in this array what to use, and then create a new object of the designated type.

set_default_package

Changes the default package for both tables and rows while adding tables.

For example:

    package My::db;

    #...

    $self->add_table('foo'); # My::foo and My::fooR
    $self->set_default_package('Your');
    $self->add_table('bar'); # Your::bar and Your::barR

get_row_type_for_table

While you should not need this, it is available to request the type name of the DB2::Row class given a table type name.

get_table

Returns the singleton table object (instantiated if necessary) given its type name. If only one table is known about that ends with the given name, it will be returned (shortcut).

For example, $mydb->get_table('Foo') will get the table object if it's really called Bar::Foo, Baz::Foo, Bar::Baz::Foo, or just Foo, but not Baz::FooBar or Baz::BarFoo. But only if there is only a single match. If there is more than one match, then the call will fail. If a case-sensitive match fails to find any matches, then a case-insensitive match is attempted.

get_table_for_row_type

Similar to get_row_type_for_table, you should not need this. Gets the table object for the given row type name.

connection

Returns the DBD::DB2 object that contains the actual connection to the database, performing the connection if required.

disconnect

Disconnects from the database (happens automatically, so shouldn't be needed).

create_db

This is used as part of the setup of the database. It will go through all the known tables and create them after first creating the database. It is assumed that the person running this has authority to do so.

To initialise your entire system, just run:

    perl -M[your_db_type] -e '[your_db_type]->create_db'

For example:

    perl -MMy::db -e 'My::db->create_db'

create_db_opts

Override this to specify any create db options during database create.

Default is to set the pagesize to 32 K.

AUTHOR

Darin McBride <dmcbride@naboo.to.org>

This framework evolved out of frustration writing reusable DDL to create tables. Once I had some objects that did that, it was slow extention to the point where they were usable for everything I could think of.

Most of the features here are because I'm incredibly lazy. I like to solve problems, but only twice. The first time is to learn it, the second time is to use my new knowledge. After that, I expect the computer to do it for me.

CREDITS

Much thanks to DB2PERL for help with the DBI, and DBD::DB2 in particular, including some bug fixes (both in DBD::DB2 and in DB2::db), and feature enhancements to DBD::DB2 that came a little earlier than originally planned.

COPYRIGHT

The DB2::db and associated modules are Copyright 2001-2008, Darin McBride. All rights reserved.

You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

BUGS

Support for using this framework on a VIEW is completely missing.

SEE ALSO

DBI, DBD::DB2