Schema Grammar

[opb]

Hi there

How thoroughly have you looked at the DB2Grammar file? For example you have the following function:

public function compileTableExists()
{
    return 'select * from information_schema.tables where table_schema = upper(?) and table_name = upper(?)';
}

But from what I can tell Db2 does not have information_schema available. Is this a known issue?

Many thanks!

[makr8100]

I tested on IBM i v7r1 and the information_schema library (schema) does not exist in a DB browser, but does return results when queried. I can get the same results and browse to it using the library SYSIBM. Are you using DB2 for i or DB2 for LUW?

[opb]

Hi Mark

Thanks for the response and sorry for the delay in getting back to you.

I'm not very familiar at all with DB2 I'm afraid. I've installed the free Express version, and used unixODBC to set up an ODBC driver (I couldn't get IBM PDO to work). I've actually hacked the ODBCConnector for the moment to just use the following to connect:

        'odbc' => [
            'driver'         => 'odbc',
            'host'           => env('DB2_HOST', 'localhost'),
            'database'       => env('DB2_DATABASE', ''),
            'username'       => env('DB2_USER', 'db2inst1'),
            'password'       => env('DB2_PASSWORD', 'password'),
            'port'           => env('DB2_PORT', 50000),
            'schema'         => 'DB2INST1',
            'odbc_driver'    => 'DB2',
        ],

    protected function getDsn(array $config)
    {
        extract($config);

        $port = isset($port) ? $port : 50000;

        return "odbc:DRIVER={".$odbc_driver."};DATABASE=$database;HOSTNAME=$host;PORT=$port;PROTOCOL=TCPIP;UID=$username;PWD=$password;";
    }

This ODBC connection does work.

I'll be looking at this again later this week, will let you know if I make any progress!

Thanks

[cooperl22]

Hi you all.

This part of Schema Grammar is kinda experimental actually. So yes you can query on information_schema but it seems that you get results about all "old" tables (I mean DDS files on IBMi environment). This behaviour is what we needed in my company at a moment.

To be honest this method should be updated to manage tables created in a "SQL way". In a "SQL way", information about schema is stored in system views of each schema of the database. Those views are named "SYS*****". For instance, if you have a schema named "MY_SCHEMA" the equivalent of information_schema to retrieve table information is "MY_SCHEMA.SYSTABLES".

Now what I'm saying is true for IBMi environment. I don't know about DB2 for LUW.

Now I've put this repo on github for sharing to the community. That said everyone is free to improve the code and make Pull Requests.

Go ahead...

[makr8100]

opb,

You are using DB2 for LUW (Linux/Unix/Windows), while cooperl22 and I are using DB2 on IBMi (AS/400). Unfortunately there have been differences between the two, and it's something we're going to have to either address, not support, or fork. I don't have DB2 on LUW to test with or do exploration of tables with. I use a program called DB Visualizer (dbvis) along with the jt400 DB2 driver to browse our databases, and maybe you can use this tool to find where this information is stored. As I said before, check in SYSIBM. If it's set up similar to how we have it on IBMi we may be able to switch the SQL over to using that library.

[opb]

Thanks for the comments and sorry for the delay in replying! I think I'm going to have to take a look at this in the coming weeks. I know very little about DB2, but a platform I'm working on will most likely be going into a DB2 LUW environment, so I'm going to have to look at getting this sorted at some point. I'll most likely hack about using my own package, and if I make any progress, I'll look at getting it into a for where it can be merged into here.

Will report back in the (hopefully) not too distant future.

[opb]

Hi guys

Actually having a bit of luck with this (against all odds)...

    /**
     * Compile the query to determine the list of tables.
     *
     * @return string
     */
    public function compileTableExists()
    {
        //return 'select * from information_schema.tables where table_schema = upper(?) and table_name = upper(?)';
        return "select * from sysibm.systables where creator like upper(?) and name like upper(?) and type like 'T'";
    }

    /**
     * Compile the query to determine the list of columns.
     *
     * @param  string  $table
     * @return string
     */
    public function compileColumnExists()
    {
        //return "select column_name from information_schema.columns where table_schema = upper(?) and table_name = upper(?)";
        return "select column_name from sysibm.syscolumns where table_schema like upper(?) and table_name like upper(?)";
    }

So far I've found one more thing I've had to change:

    /**
     * Get the SQL for an auto-increment column modifier.
     *
     * @param  \Illuminate\Database\Schema\Blueprint  $blueprint
     * @param  \Illuminate\Support\Fluent  $column
     * @return string|null
     */
    protected function modifyIncrement(Blueprint $blueprint, Fluent $column)
    {
        if (in_array($column->type, $this->serials) && $column->autoIncrement)
        {
            //return ' as identity';
            return ' generated always as identity';
        }
    }

Though I do need to go through properly and have a look for more issues.

Also worth noting that I've had to use the EasySoft proprietary ODBC DB2 driver, as the one I created using unixODBC just hasn't been working so far (just in case anyone reading this has similar issues with LUW).

[opb]

Hi @makr8100 @cooperl22

I've made a bit of progress at this end, though am finding some things frustrating - e.g, for some reason indexes aren't being created on my tables, even why using raw SQL (so nothing to do with the package). To be honest, my lack of experience with DB2 has been hindering me here.

Are either of you able to (or know of anyone who can) spend time getting this working for LUW as paid work?

thanks

[makr8100]

I'm not familiar enough with DB2 on LUW, but posted for help here: http://forums.zend.com/viewtopic.php?f=63&t=127668 Generally that would be considered spam on this forum but I'm hoping they'll forgive me in the interest of getting some OSS over to IBM platforms.

[opb]

Hi Mark

Thanks for doing that. I might end up having to turn to Odesk! Will let you know how I get on.

[opb]

So, I've got migrations working. the only thing that doesn't seem to work at this stage is the select id from new table (insert....) syntax, when using timestamps. Timestamps are fine in a normal insert query, but when you wrap it in the select id from new table it bails.

I posted to SO because it seems to be a PDO or issue as opposed to this package: http://stackoverflow.com/questions/31323567/db2-timestamp-format-in-php-pdo