HPL Question
Someone on IDS 9.4/HP-UX wanted HPL to unload a table in a different layout (reordered columns plus extra constant columns), but HPL kept using the table's native column format despite a custom SELECT. Replies explained the proper HPL procedure: create a default map with onpladm create map, dump the query with onpladm describe query to a config file, edit the SELECTSTATEMENT line to add the constants/where clause, apply it with onpladm modify object, then run onpload with that map. Another poster suggested dropping the 'as' keyword (and using real constants like '1'/'2') in the select list, which worked in her test. The original poster said he'd retry but didn't confirm the outcome.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm running IDS 9.4 on HP-UX. I'd like to be able to use the HPL tool to unload a table into a different format. Table1 (a,b,c,d) unload the data into (a,b,1,2,c,d) I tried "select a,b, ' ' as col1, ' ' as col2,c,d, from ps_ledger;" but HPL appears to only unload the data in the format of the table (a,b,c,d) Have any of you tried using HPL to unload into a different format? Thanks
Using Informix HPL With a Where Clause
If you want to use the Informix High-Performance Loader (HPL) with a where
clause, there are a few steps you need to follow.
Create the "default" map for the table you want to partially unload. Use the
command onpladm create map ld_tabname -D dbname -t tabname -z D
You then need to "describe" the query that was created for the default map,
and save the configuration to a file. Use the command onpladm describe query
ld_tabname -F config-file-name
Edit config-file-name, and go to the line that contains the text
SELECTSTATEMENT. Simply add your where-clause, or in your case, add in the
constants, to the end of the select-statement that is listed (it's probably a
select * from tabname).You must then modify the query that you just described. Use the command
onpladm modify object -F config-file-name
Once the query has been modified, you can unload the data, using HPL. Assuming
that you want the extract to be automatically compressed, use the following
command:
onpload -m ld_tabname -d "compress -c > target-dir/tabname_ld.Z" -fup -i 100000
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jerry
Hamilton
Sent: Thursday, March 19, 2009 8:52 AM
To: ids@iiug.org
Subject: HPL Question [15242]
I'm running IDS 9.4 on HP-UX.
I'd like to be able to use the HPL tool to unload a table into a different
format.
Table1 (a,b,c,d) unload the data into (a,b,1,2,c,d)
I tried "select a,b, ' ' as col1, ' ' as col2,c,d, from ps_ledger;"
but HPL appears to only unload the data in the format of the table (a,b,c,d)
Have any of you tried using HPL to unload into a different format?
Thanks
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi, Have a look at: http://www.ibmdatabasemag.com/blog/main/archives/2008/07/the_informix_hi_2.html Regards Vikas
Hi, It should be "select a,b, '1' as col1, '2' as col2,c,d, from ps_ledger;" but I'm not sure you want to fix only '1' and '2' or not. Jakkrit A. ________________________________ From: Jerry Hamilton <bigdaddyjerry@yahoo.com> To: ids@iiug.org Sent: Thursday, March 19, 2009 8:52:18 PM Subject: HPL Question [15242] I'm running IDS 9.4 on HP-UX. I'd like to be able to use the HPL tool to unload a table into a different format. Table1 (a,b,c,d) unload the data into (a,b,1,2,c,d) I tried "select a,b, ' ' as col1, ' ' as col2,c,d, from ps_ledger;" but HPL appears to only unload the data in the format of the table (a,b,c,d) Have any of you tried using HPL to unload into a different format? Thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hey Jerry!
How ya been?
I just tried this (in Server Studio) and it worked... change up your sql just
a bit..
select a,b, ' ' col1, ' ' col2,c,d, from ps_ledger - take out the 'as'
With my table, I got the columns in diff order and a constant too...
Let me know if it works..
Laurie
>>> "Jerry Hamilton" <bigdaddyjerry@yahoo.com> 3/19/2009 7:52 AM >>>
I'm running IDS 9.4 on HP-UX.
I'd like to be able to use the HPL tool to unload a table into a different
format.
Table1 (a,b,c,d) unload the data into (a,b,1,2,c,d)
I tried "select a,b, ' ' as col1, ' ' as col2,c,d, from ps_ledger;"
but HPL appears to only unload the data in the format of the table (a,b,c,d)
Have any of you tried using HPL to unload into a different format?
Thanks
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hey Laurie,
Thanks for the reply. I'm doing ok. A lot of layoffs here, making my job
really nasty. I'm working 55 - 60 hours a week trying to keep up. No help in
site for awhile either. Stupid economy.
I thought I tried taking the "as" out... Who knows, I'm so frazzled I can't
even remember what I've tried and what I haven't.
I'll give it another spin.
Talk to you later.
--- On Thu, 3/19/09, Laurie Gustin <lgustin@utah.gov> wrote:
> From: Laurie Gustin <lgustin@utah.gov>
> Subject: Re: HPL Question [15252]
> To: ids@iiug.org
> Date: Thursday, March 19, 2009, 12:48 PM
> Hey Jerry!
>
> How ya been?
>
> I just tried this (in Server Studio) and it worked...
> change up your sql just
> a bit..
>
> select a,b, ' ' col1, ' ' col2,c,d, from ps_ledger - take> out the 'as'
>
> With my table, I got the columns in diff order and a
> constant too...
>
> Let me know if it works..
>
> Laurie
>
> >>> "Jerry Hamilton" <bigdaddyjerry@yahoo.com>
> 3/19/2009 7:52 AM >>>
>
> I'm running IDS 9.4 on HP-UX.
>
> I'd like to be able to use the HPL tool to unload a table
> into a different
> format.
>
> Table1 (a,b,c,d) unload the data into (a,b,1,2,c,d)
>
> I tried "select a,b, ' ' as col1, ' ' as col2,c,d, from
> ps_ledger;"
>
> but HPL appears to only unload the data in the format of
> the table (a,b,c,d)
>
> Have any of you tried using HPL to unload into a different
> format?
>
> Thanks
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the
> discussion forum.
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the
> discussion forum.
>
>