Thread: Migration from DB2 to PostgreSQL

Migration from DB2 to PostgreSQL

From
sachin kotwal
Date:
While migrating my application from DB2 to PostgreSQL.

I want to migrate following functions in PostgreSQL.

TO_CHAR() in DB2 which can take three arguments as follows:

SELECT TO_CHAR(CURRENT_DATE,'YYYY-MM-DD',112.50) FROM SYSIBM.SYSDUMMY1

I am not sure what is the purpose of third argument in TO_CHAR() function of
DB2.

I think we can create one user define function with name TO_CHAR which can
take three argument like DB2.

I want to know that if we want to create user define function TO_CHAR().
What should be the data type of third argument?

which can handle maximum data types and more suitable for real time
scenarios.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-tp5759820.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: Migration from DB2 to PostgreSQL

From
Albe Laurenz
Date:
sachin kotwal wrote:
> While migrating my application from DB2 to PostgreSQL.
> 
> I want to migrate following functions in PostgreSQL.
> 
> TO_CHAR() in DB2 which can take three arguments as follows:
> 
> SELECT TO_CHAR(CURRENT_DATE,'YYYY-MM-DD',112.50) FROM SYSIBM.SYSDUMMY1
> 
> I am not sure what is the purpose of third argument in TO_CHAR() function of
> DB2.
> 
> I think we can create one user define function with name TO_CHAR which can
> take three argument like DB2.
> 
> I want to know that if we want to create user define function TO_CHAR().
> What should be the data type of third argument?
> 
> which can handle maximum data types and more suitable for real time
> scenarios.

Look at IBM's documentation at
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0007110.html

TO_CHAR is a synonym for VARCHAR_FORMAT.
The third argument is an optional locale name, like 'en_US',
which will be used to generate the text parts of the
format you chose.

For a format like 'YYYY-MM-DD' this has no effect at all
and can be omitted.

PostgreSQL has no such capability.  Unless you need that and
want to code it yourself, the best solution would be to
write a function that just ignores the third argument.

Yours,
Laurenz Albe

Re: Migration from DB2 to PostgreSQL

From
Amit Langote
Date:
On Wed, Jun 19, 2013 at 6:00 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> sachin kotwal wrote:
>> While migrating my application from DB2 to PostgreSQL.
>>
>> I want to migrate following functions in PostgreSQL.
>>
>> TO_CHAR() in DB2 which can take three arguments as follows:
>>
>> SELECT TO_CHAR(CURRENT_DATE,'YYYY-MM-DD',112.50) FROM SYSIBM.SYSDUMMY1
>>
>> I am not sure what is the purpose of third argument in TO_CHAR() function of
>> DB2.
>>
>> I think we can create one user define function with name TO_CHAR which can
>> take three argument like DB2.
>>
>> I want to know that if we want to create user define function TO_CHAR().
>> What should be the data type of third argument?
>>
>> which can handle maximum data types and more suitable for real time
>> scenarios.
>
> Look at IBM's documentation at
> http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0007110.html
>
> TO_CHAR is a synonym for VARCHAR_FORMAT.
> The third argument is an optional locale name, like 'en_US',
> which will be used to generate the text parts of the
> format you chose.
>
> For a format like 'YYYY-MM-DD' this has no effect at all
> and can be omitted.
>
> PostgreSQL has no such capability.  Unless you need that and
> want to code it yourself, the best solution would be to
> write a function that just ignores the third argument.
>

Just wondering:

If this particular function is to be used repeatedly in a single
query, would the cost of having a wrapper function around the original
function be too large? For example, if this function appears in a
WHERE clause against a table containing millions of rows.


--
Amit Langote


Re: Migration from DB2 to PostgreSQL

From
Chris Angelico
Date:
On Thu, Jun 20, 2013 at 11:10 AM, Amit Langote <amitlangote09@gmail.com> wrote:
> If this particular function is to be used repeatedly in a single
> query, would the cost of having a wrapper function around the original
> function be too large? For example, if this function appears in a
> WHERE clause against a table containing millions of rows.

If your wrapper function is written in SQL and is trivial (eg ignore
the third parameter and pass the other two on), the planner should be
able to optimize right through it. Best way to find out is with
EXPLAIN, which I've been using a good bit lately. The optimizer's
pretty smart.

ChrisA


Re: Migration from DB2 to PostgreSQL

From
Amit Langote
Date:
Hi,

On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico <rosuav@gmail.com> wrote:
> On Thu, Jun 20, 2013 at 11:10 AM, Amit Langote <amitlangote09@gmail.com> wrote:
>> If this particular function is to be used repeatedly in a single
>> query, would the cost of having a wrapper function around the original
>> function be too large? For example, if this function appears in a
>> WHERE clause against a table containing millions of rows.
>
> If your wrapper function is written in SQL and is trivial (eg ignore
> the third parameter and pass the other two on), the planner should be
> able to optimize right through it. Best way to find out is with
> EXPLAIN, which I've been using a good bit lately. The optimizer's
> pretty smart.

For example consider following rough example:

postgres=# create table nums as select * from generate_series(1,1000000) as num;
SELECT
Time: 1185.589 ms
postgres=# select count(*) from nums where num > 3450;
 count
--------
 996550
(1 row)

Time: 183.987 ms

postgres=# create or replace function gt(n int, m int) returns boolean as $$
begin
return n > m;
end;
$$
language plpgsql;
CREATE FUNCTION
Time: 1.080 ms

postgres=# select count(*) from nums where gt(num, 3450);
 count
--------
 996550
(1 row)

Time: 1327.800 ms

postgres=# create or replace function gt3(n int, m int, o int) returns
boolean as $$
begin
return gt(n, m);
end;
$$
language plpgsql;
CREATE FUNCTION
Time: 1.073 ms

postgres=# select count(*) from nums where gt3(num, 3450, 0);
 count
--------
 996550
(1 row)

Time: 2356.576 ms

postgres=# explain select count(*) from nums where gt3(num, 3450, 0);
                             QUERY PLAN
--------------------------------------------------------------------
 Aggregate  (cost=265258.34..265258.35 rows=1 width=0)
   ->  Seq Scan on nums  (cost=0.00..264425.00 rows=333333 width=0)
         Filter: gt3(num, 3450, 0)
(3 rows)


--
Amit Langote


Re: Migration from DB2 to PostgreSQL

From
Chris Angelico
Date:
On Thu, Jun 20, 2013 at 11:35 AM, Amit Langote <amitlangote09@gmail.com> wrote:
> On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico <rosuav@gmail.com> wrote:
>> If your wrapper function is written in SQL and is trivial (eg ignore
>> the third parameter and pass the other two on), the planner should be
>> able to optimize right through it. Best way to find out is with
>> EXPLAIN, which I've been using a good bit lately. The optimizer's
>> pretty smart.
>
> For example consider following rough example:
>
> postgres=# create table nums as select * from generate_series(1,1000000) as num;
> SELECT
> Time: 1185.589 ms
> postgres=# select count(*) from nums where num > 3450;
>  count
> --------
>  996550
> (1 row)
>
> Time: 183.987 ms
>
> postgres=# create or replace function gt(n int, m int) returns boolean as $$
> begin
> return n > m;
> end;
> $$
> language plpgsql;
> CREATE FUNCTION
> Time: 1.080 ms
>
> postgres=# select count(*) from nums where gt(num, 3450);
>  count
> --------
>  996550
> (1 row)
>
> Time: 1327.800 ms
>

Huge difference between 'language plpgsql' and 'language sql'. Here's
my timings using your code - similar to your timings:

rosuav=> select count(*) from nums where num > 3450;
 count
--------
 996550
(1 row)

Time: 293.836 ms
rosuav=> select count(*) from nums where gt(num, 3450);
 count
--------
 996550
(1 row)

Time: 2412.186 ms
rosuav=> select count(*) from nums where gt3(num, 3450, 0);
 count
--------
 996550
(1 row)

Time: 4332.554 ms

Now here's the SQL version of the code:

rosuav=> create or replace function gt(n int, m int) returns boolean
as 'select n>m' language sql;
CREATE FUNCTION
Time: 39.196 ms
rosuav=> select count(*) from nums where gt(num, 3450);
 count
--------
 996550
(1 row)

Time: 258.153 ms
rosuav=> create or replace function gt3(n int, m int,o int) returns
boolean as 'select gt(n,m)' language sql;
CREATE FUNCTION
Time: 21.891 ms
rosuav=> select count(*) from nums where gt3(num, 3450, 0);
 count
--------
 996550
(1 row)

Time: 258.998 ms

The original, the one with the SQL function, and the one with two SQL
functions, all are within margin of error. (Repeated execution shows
times varying down as far as 237ms for the last one.)

ChrisA


Re: Migration from DB2 to PostgreSQL

From
Amit Langote
Date:
On Thu, Jun 20, 2013 at 10:54 AM, Chris Angelico <rosuav@gmail.com> wrote:
> On Thu, Jun 20, 2013 at 11:35 AM, Amit Langote <amitlangote09@gmail.com> wrote:
>> On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico <rosuav@gmail.com> wrote:
>>> If your wrapper function is written in SQL and is trivial (eg ignore
>>> the third parameter and pass the other two on), the planner should be
>>> able to optimize right through it. Best way to find out is with
>>> EXPLAIN, which I've been using a good bit lately. The optimizer's
>>> pretty smart.
>>
>> For example consider following rough example:
>>
>> postgres=# create table nums as select * from generate_series(1,1000000) as num;
>> SELECT
>> Time: 1185.589 ms
>> postgres=# select count(*) from nums where num > 3450;
>>  count
>> --------
>>  996550
>> (1 row)
>>
>> Time: 183.987 ms
>>
>> postgres=# create or replace function gt(n int, m int) returns boolean as $$
>> begin
>> return n > m;
>> end;
>> $$
>> language plpgsql;
>> CREATE FUNCTION
>> Time: 1.080 ms
>>
>> postgres=# select count(*) from nums where gt(num, 3450);
>>  count
>> --------
>>  996550
>> (1 row)
>>
>> Time: 1327.800 ms
>>
>
> Huge difference between 'language plpgsql' and 'language sql'. Here's
> my timings using your code - similar to your timings:
>

Umm, my bad! I almost forgot I could write pure SQL function bodies.
Although, why does following happen? (sorry, a 8.4.2 installation) :

postgres=# create or replace function gt(n int, m int) returns boolean
as 'select n>m' language sql;
ERROR:  column "n" does not exist
LINE 2: as 'select n>m' language sql;


--
Amit Langote


Re: Migration from DB2 to PostgreSQL

From
Chris Angelico
Date:
On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote <amitlangote09@gmail.com> wrote:
> Umm, my bad! I almost forgot I could write pure SQL function bodies.
> Although, why does following happen? (sorry, a 8.4.2 installation) :
>
> postgres=# create or replace function gt(n int, m int) returns boolean
> as 'select n>m' language sql;
> ERROR:  column "n" does not exist
> LINE 2: as 'select n>m' language sql;

Hmm, no idea. I'm using 9.2.4, could well have been changes.

ChrisA


Re: Migration from DB2 to PostgreSQL

From
Amit Langote
Date:
On Thu, Jun 20, 2013 at 11:10 AM, Chris Angelico <rosuav@gmail.com> wrote:
> On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote <amitlangote09@gmail.com> wrote:
>> Umm, my bad! I almost forgot I could write pure SQL function bodies.
>> Although, why does following happen? (sorry, a 8.4.2 installation) :
>>
>> postgres=# create or replace function gt(n int, m int) returns boolean
>> as 'select n>m' language sql;
>> ERROR:  column "n" does not exist
>> LINE 2: as 'select n>m' language sql;
>
> Hmm, no idea. I'm using 9.2.4, could well have been changes.
>

Hmm, I guess in 8.4.2, one needs to refer to function arguments as $1, $2 ...

postgres=# create or replace function gt(n int, m int) returns boolean
as 'select $1>$2' language sql;
CREATE FUNCTION

postgres=# create or replace function gt3(n int, m int, o int) returns boolean
as 'select gt($1,$2)' language sql;
CREATE FUNCTION

postgres=# select count(*) from nums where num > 3450;
 count
--------
 996550
(1 row)

Time: 126.184 ms

postgres=# select count(*) from nums where gt(num, 3450);
 count
--------
 996550
(1 row)

Time: 130.754 ms

postgres=# select count(*) from nums where gt3(num, 3450, 0);
 count
--------
 996550
(1 row)

Time: 140.031 ms


And yes. OP can go ahead with his migration using this suggested
wrapping function idea.


--
Amit Langote


Re: Migration from DB2 to PostgreSQL

From
Chris Angelico
Date:
On Thu, Jun 20, 2013 at 12:34 PM, Amit Langote <amitlangote09@gmail.com> wrote:
> On Thu, Jun 20, 2013 at 11:10 AM, Chris Angelico <rosuav@gmail.com> wrote:
>> On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote <amitlangote09@gmail.com> wrote:
>>> Umm, my bad! I almost forgot I could write pure SQL function bodies.
>>> Although, why does following happen? (sorry, a 8.4.2 installation) :
>>>
>>> postgres=# create or replace function gt(n int, m int) returns boolean
>>> as 'select n>m' language sql;
>>> ERROR:  column "n" does not exist
>>> LINE 2: as 'select n>m' language sql;
>>
>> Hmm, no idea. I'm using 9.2.4, could well have been changes.
>>
>
> Hmm, I guess in 8.4.2, one needs to refer to function arguments as $1, $2 ...

Ah, okay. I'm not all that familiar with different versions of
PostgreSQL; I used it first back in the 1990s, then didn't use it for
years (was all DB2), and now picked it up again at version 9.0/9.1,
moving to 9.2 when it came out.

> And yes. OP can go ahead with his migration using this suggested
> wrapping function idea.

Absolutely!

ChrisA


Re: Migration from DB2 to PostgreSQL

From
sachin kotwal
Date:
>PostgreSQL has no such capability.  Unless you need that and
>want to code it yourself, the best solution would be to
>write a function that just ignores the third argument.

For time being I will write a function that just ignores the third argument.

but if we really want to create such function like DB2 TO_CHAR()  we need to
code it.

like setting locale as third argument or format string according to third
argument and return it.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-tp5759820p5760265.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.