luciddb-users — Mailing list for users of LucidDB

Re: [luciddb-users] Adding columns to existing table

[John V. S. <js...@gm...>]

Vassil Kovatchev wrote:
> Hi All,
> 
> I think I've read somewhere something about adding columns to 
> pre-existing tables in LucidDb, but of course now I cannot find it.
> I tried the standard ALTER TABLE SQL statement, but that failed, so I 
> think there was something special about the way columns were added to a 
> table - LucidDb being a columns-store RDBMS and all.
> Anyone know how to do this without dropping and recreating the table?

ALTER TABLE ADD COLUMN is one of the standard features which remains to 
be implemented (see the Roadmap link from the LucidDB home page).

So for now it is necessary to create a new table, move data, and then 
drop the old one.  Unfortunately this also requires dropping and 
recreating dependent objects such as views.

It is true that column store makes possible a very efficient ADD COLUMN 
(create a new column cluster without touching the existing ones); we 
just haven't gotten to it yet.

JVS

[luciddb-users] Adding columns to existing table

[Vassil K. <vas...@gm...>]

Hi All,

I think I've read somewhere something about adding columns to pre-existing
tables in LucidDb, but of course now I cannot find it.
I tried the standard ALTER TABLE SQL statement, but that failed, so I think
there was something special about the way columns were added to a table -
LucidDb being a columns-store RDBMS and all.
Anyone know how to do this without dropping and recreating the table?

Any help is appreciated.

Thanks,
Vassil

[luciddb-users] LucidDB 0.8.0 release is now available

[John V. S. <js...@gm...>]

Download links are available from http://www.luciddb.org, or from 
http://sourceforge.net/projects/luciddb.

The LucidDB installer still does not support automatic upgrade, so be
sure to install the new release in a new location if you already have an
old version installed.

There is a manual upgrade procedure:

http://docs.eigenbase.org/LucidDbUpgrade

However, due to some catalog model changes in 0.8.0, it will fail for 
the 0.7.4->0.8.0 path.  If you are relying on this procedure, let me 
know and I'll update that page with one-off CREATE OR REPLACE VIEW fixup 
instructions to be performed on the 0.7.4 instance before starting the 
upgrade.

Major new features in this release are warehouse labels and 
hot/incremental backup, inching us closer to "1.0" status (only two more 
tenths of a whole release number to go!).

The binary packages are still built with JDK 1.5, but from a source 
package, it is now possible to build with JDK 1.6, so give that a try if 
you are integrating LucidDB with other components which require a 1.6 JVM.

Incidentally, for anyone curious about the LucidDB/Eigenbase release 
procedure, I've documented it here:

http://pub.eigenbase.org/wiki/ReleaseProcedure

Changes since 0.7.4 include:

- add new feature for warehouse labels
- add new feature for hot/incremental/differential backup/restore
- fix timestamp fractional seconds formatting when cast to VARCHAR
- add new applib.contiguous_value_intervals UDX
- add new sys_root.test_data_server and
   sys_root.test_all_servers_for_wrapper system procedures for
   testing links to SQL/MED data servers
- add ext directories (e.g. for moodss) to distribution (LDB-189)
- fix uninitialized variable which could cause bogus values
   for page allocation performance counters (FNL-78)
- rename non-standard POW function to SQL:2003 standard POWER (FRG-329)
- improve join ordering to use column count when number of rows is the same
- fix for join order optimization when multiple join factors have same 
weight
- add fifo support to flaftile reader
- add Mondrian aggregate designer support
- suppress AWT workaround in distribution startup scripts (FRG-350)
- ignore semijoin candidates with poor selectivity
- fix validator to prevent duplicate aliases in the same FROM clause
- JDK 1.6 build support
- add table prefix mapping support to SQL/MED JDBC wrapper
- add JNDI data source and connection pool support to SQL/MED JDBC wrapper
- fix SQL/MED JDBC wrapper pushdown bug with WHERE boolean_column (FRG-339)
- optimize away redundant buffering for upserts, deletes, and self-inserts
- fix shutdown warning from loopback connections (FRG-345)
- TPC-H query phrasing refinements
- fix bug with default values in INSERT/MERGE with UNION source
- fix flatfile reader bug where csv header row has missing column names
- fix for correlated subqueries on SQL/MED JDBC foreign tables
- fix issues with UDX input restarts (FRG-331)
- improve agg-distinct rewrite to consolidate common subexpressions
- fix native code error message rendering (LDB-177)
- use statement-level queues for all warnings
- fix executor inefficiency from accidental Boost graph copying
- add support for NATURAL JOIN
- optimize away some redundant casts
- lucidDbAllocFile script usage/validation improvements
- improve optimizer to reorder index intersections by selectivity

JVS

Re: [luciddb-users] Missing BACKUP_DATABASE/RESTORE_DATABASE routines?

[Vassil K. <vas...@gm...>]

Thanks much, John!

Regards,
Vassil

On Mon, Nov 10, 2008 at 1:03 PM, John V. Sichi <js...@gm...> wrote:

> Vassil Kovatchev wrote:
>
>> Hi All,
>>
>> Today I tried to backup my LucidDB 0.7.4 instance and to my surprise I
>> found out that the backup/restore routines were missing from the SYS_ROOT
>> schema.
>> I ran the following SQL statement on sqllineClient:
>> call SYS_ROOT.BACKUP_DATABASE('/usr/luciddb-0.7.4/catalog/full_archive',
>> 'FULL', 'COMPRESSED');
>> ...which yielded the following message:
>> Error: From line 1, column 15 to line 1, column 94: No match found for
>> function signature BACKUP_DATABASE(<CHARACTER>, <CHARACTER>, <CHARACTER>)
>> (state=,code=0)
>>
>
> BACKUP/RESTORE is not in the 0.7.4 release.  It is available for testing in
> the 0.7.5 prerelease:
>
> http://perforce.eigenbase.org/download/luciddb-bin-linux32-0.7.5.tar.bz2
> http://perforce.eigenbase.org/download/luciddb-bin-linux64-0.7.5.tar.bz2
>
> JVS
>

Re: [luciddb-users] Missing BACKUP_DATABASE/RESTORE_DATABASE routines?

[John V. S. <js...@gm...>]

Vassil Kovatchev wrote:
> Hi All,
> 
> Today I tried to backup my LucidDB 0.7.4 instance and to my surprise I 
> found out that the backup/restore routines were missing from the 
> SYS_ROOT schema.
> I ran the following SQL statement on sqllineClient:
> call SYS_ROOT.BACKUP_DATABASE('/usr/luciddb-0.7.4/catalog/full_archive', 
> 'FULL', 'COMPRESSED');
> ...which yielded the following message:
> Error: From line 1, column 15 to line 1, column 94: No match found for 
> function signature BACKUP_DATABASE(<CHARACTER>, <CHARACTER>, 
> <CHARACTER>) (state=,code=0)

BACKUP/RESTORE is not in the 0.7.4 release.  It is available for testing 
in the 0.7.5 prerelease:

http://perforce.eigenbase.org/download/luciddb-bin-linux32-0.7.5.tar.bz2
http://perforce.eigenbase.org/download/luciddb-bin-linux64-0.7.5.tar.bz2

JVS

[luciddb-users] Missing BACKUP_DATABASE/RESTORE_DATABASE routines?

[Vassil K. <vas...@gm...>]

Hi All,

Today I tried to backup my LucidDB 0.7.4 instance and to my surprise I found
out that the backup/restore routines were missing from the SYS_ROOT schema.
I ran the following SQL statement on sqllineClient:
call SYS_ROOT.BACKUP_DATABASE('/usr/luciddb-0.7.4/catalog/full_archive',
'FULL', 'COMPRESSED');
...which yielded the following message:
Error: From line 1, column 15 to line 1, column 94: No match found for
function signature BACKUP_DATABASE(<CHARACTER>, <CHARACTER>, <CHARACTER>)
(state=,code=0)

When I checked the list of registered routines by running SQL:
select * from SYS_ROOT.dba_routines;

I got the following list of routines where I cannot see BACKUP_DATABASE, nor
can I see RESTORE_DATABASE.
Am I missing something?

The list of routines I'm getting is:

ADD_DAYS
ADD_HOURS
CALENDAR_QUARTER
CHAR_REPLACE
CHAR_TO_DATE
CHAR_TO_TIME
CHAR_TO_TIMESTAMP
CLEAN_PHONE
CLEAN_PHONE_INTERNATIONAL
CONTAINS_NUMBER
CONVERT_DATE
CURRENT_DATE_IN_JULIAN
DATE_TO_CHAR
DAY_FROM_JULIAN_START
DAY_IN_YEAR
DAY_NUMBER_OVERALL
DAYS_DIFF
FISCAL_MONTH
FISCAL_QUARTER
FISCAL_YEAR
HOURS_DIFF
INTERNAL_DATE
JULIAN_DAY_TO_DATE
JULIAN_DAY_TO_TIMESTAMP
LEFTN
RAND
REPEATER
RIGHTN
STR_REPLACE
TIME_TO_CHAR
TIMESTAMP_TO_CHAR
CONVERT_CWM_INDEX_ATTRIBUTES_TO_TYPE
CONVERT_CWM_NULLABLE_TO_INT
CONVERT_CWM_NULLABLE_TO_STRING
CONVERT_CWM_PARAM_KIND_TO_INT
CONVERT_CWM_STATISTIC_TO_INT
CONVERT_CWM_TYPENAME_TO_LITERAL_PREFIX
CONVERT_CWM_TYPENAME_TO_LITERAL_SUFFIX
NULL_IDENTIFIER
NULL_REMARKS
BROWSE_CONNECT_FOREIGN_SERVER
BROWSE_FOREIGN_SCHEMAS
CHAR_TO_DATE
CHAR_TO_TIME
CHAR_TO_TIMESTAMP
DATE_TO_CHAR
EXPORT_CATALOG_XMI
EXPORT_QUERY_TO_FILE
EXPORT_SCHEMA_TO_FILE
FLUSH_CODE_CACHE
GET_TABLE_TYPE_BY_MOF_CLASS_NAME
KILL_SESSION
KILL_STATEMENT
KILL_STATEMENT_MATCH
OBJECTS_IN_USE
PERFORCE_CHANGELISTS
PERFORMANCE_COUNTERS
REPOSITORY_INTEGRITY_VIOLATIONS
REPOSITORY_LOB_TEXT
REPOSITORY_PROPERTIES
SESSION_PARAMETERS
SESSIONS
SET_OPT_RULE_DESC_EXCLUSION_FILTER
SLEEP
STAT_GET_CARDINALITY
STAT_GET_ROW_COUNT
STAT_GET_SELECTIVITY
STAT_SET_COLUMN_HISTOGRAM
STAT_SET_PAGE_COUNT
STAT_SET_ROW_COUNT
STATEMENTS
SYSTEM_INFO
THREAD_STACK_ENTRIES
THREADS
TIME_TO_CHAR
TIMESTAMP_TO_CHAR
ALTER_JAVA_PATH
INSTALL_JAR
REMOVE_JAR
REPLACE_JAR
UPDATE_CONFIGURATION
UPDATE_SYSTEM_OBJECTS

Thanks in advance,
Vassil

Re: [luciddb-users] multiple databases

[John V. S. <js...@gm...>]

Nicholas Goodman wrote:
> If there was a way to, as part of the label creation logic, create a 
> snapshot of the catalog at that point in time it could make hot 
> backups/moving/etc easier.  As a warehouse user, knowing that 
> absolutely, 100% hot backups are not really needed, the label feels like 
> it could be part of a great asynchronous backup/movement/replication/etc 
> framework.  Create the label and a scheduled process simply periodically 
> creates backups "up to" the latest label (but not current which is more 
> complicated.

Have you checked out Zelaine's backup spec?

http://pub.eigenbase.org/wiki/LucidDbBackupRestore

This is already available for testing in the 0.7.5 prerelease, and 
includes hot and incremental backup.  It should already be possible to 
use incremental for replication too.

If you kick off a backup immediately after creating a label, I think you 
already get pretty much what you're after, right?

JVS

Re: [luciddb-users] multiple databases

[Nicholas G. <ngo...@ba...>]

On Nov 4, 2008, at 5:06 PM, John V. Sichi wrote:

> work, and also decide on the best way to support the "movable" use  
> case
> you mention (for that, a correspondence between logical schema and
> physical tablespace is desirable, whereas for general purposes it's  
> nice
> to keep physical and logical containment independent).


<random-thoughts>
What about keeping an export of the catalog to go along with every  
label?  The label seems like a natural way to do backups/checkpoints  
in addition to read/load consistency.

If there was a way to, as part of the label creation logic, create a  
snapshot of the catalog at that point in time it could make hot  
backups/moving/etc easier.  As a warehouse user, knowing that  
absolutely, 100% hot backups are not really needed, the label feels  
like it could be part of a great asynchronous backup/movement/ 
replication/etc framework.  Create the label and a scheduled process  
simply periodically creates backups "up to" the latest label (but not  
current which is more complicated.
</random-thoughts>

Nick

Re: [luciddb-users] multiple databases

[John V. S. <js...@gm...>]

There is no technical reason; the underlying components are already 
designed for it in Fennel.  We do need to figure out how some of the 
newer features (warehouse labels and backup/restore) intersect with this 
work, and also decide on the best way to support the "movable" use case 
you mention (for that, a correspondence between logical schema and 
physical tablespace is desirable, whereas for general purposes it's nice 
to keep physical and logical containment independent).

JVS

Philip Caithness wrote:
> Hi All,
>  
> Outside of effort and manpower, is there any technical reason why it 
> would not be possible to extend LucidDB to allow schemas to be stored in 
> their own dat files ?
>  
> I am looking at using LucidDB in an OLAP server, and we have a 
> requirement to store each dataset as a seperate file so it can be moved 
> onto other server instances.
> Some of our customers have many data sets (100's +).
>  
> It would involve some sort of attatch and detatch mechanism. Not having 
> been near the sourc code yet I am not sure how entrenched the single 
> file concept is.
>  
> Is the file per schema concept remotely feasible, or just not worth even 
> considering ?
> I can see that others have asked similar questions on the list, but here 
> is no indication of complexity.
>  
> thanks,
> Phil
> 
> 
> ------------------------------------------------------------------------
> 
> -------------------------------------------------------------------------
> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> Build the coolest Linux based applications with Moblin SDK & win great prizes
> Grand prize is a trip for two to an Open Source event anywhere in the world
> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> luciddb-users mailing list
> luc...@li...
> https://lists.sourceforge.net/lists/listinfo/luciddb-users

[luciddb-users] multiple databases

[Philip C. <Phi...@st...>]

Hi All,
 
Outside of effort and manpower, is there any technical reason why it would not be possible to extend LucidDB to allow schemas to be stored in their own dat files ?
 
I am looking at using LucidDB in an OLAP server, and we have a requirement to store each dataset as a seperate file so it can be moved onto other server instances.
Some of our customers have many data sets (100's +).
 
It would involve some sort of attatch and detatch mechanism. Not having been near the sourc code yet I am not sure how entrenched the single file concept is.
 
Is the file per schema concept remotely feasible, or just not worth even considering ?
I can see that others have asked similar questions on the list, but here is no indication of complexity.
 
thanks,
Phil

Re: [luciddb-users] LucidDbMonitor

[Vassil K. <vas...@gm...>]

Many thanks, John!

Vassil

On Mon, Nov 3, 2008 at 9:12 PM, John V. Sichi <js...@gm...> wrote:

> Vassil Kovatchev wrote:
>
>> The first problem I had was that there is no Ubuntu installation package
>> named "moodss" so "apt-get install moodss" did not help much.
>>
>
> Hmmm...it was there up through Gutsy, but seems to be gone in Hardy. Not
> sure why they left it out.
>
> http://packages.ubuntu.com/gutsy/admin/moodss
> http://packages.ubuntu.com/hardy/admin/moodss
>
>  But the worst issue is, I cannot find file "lucidDbMonitor.sh". It's not
>> clear from the doc page <http://pub.eigenbase.org/wiki/LucidDbMonitor> if
>> this file is supposed to be part of the LucidDB installation or the moodss
>> installation...(I have LucidDB version 0.7.4 and moodss version 21.5 - this
>> file is located in neither of these installations - or at least I could not
>> find it...).
>>
>
> It was supposed to be part of the LucidDB installation, but...whoops,
> nothing under the ext directory is currently getting included.
>
> For now, I've packed it up separately and you can download it from this
> link:
>
> http://pub.eigenbase.org/mediawiki/upload/d/d9/Luciddb-monitor.tar.bz2
>
> I've also updated the wiki page.  I'll fix the distribution problem for the
> next release.
>
> JVS
>

Re: [luciddb-users] LucidDbMonitor

[John V. S. <js...@gm...>]

Vassil Kovatchev wrote:
> The first problem I had was that there is no Ubuntu installation package 
> named "moodss" so "apt-get install moodss" did not help much.

Hmmm...it was there up through Gutsy, but seems to be gone in Hardy. 
Not sure why they left it out.

http://packages.ubuntu.com/gutsy/admin/moodss
http://packages.ubuntu.com/hardy/admin/moodss

> But the worst issue is, I cannot find file "lucidDbMonitor.sh". It's not 
> clear from the doc page <http://pub.eigenbase.org/wiki/LucidDbMonitor> 
> if this file is supposed to be part of the LucidDB installation or the 
> moodss installation...(I have LucidDB version 0.7.4 and moodss version 
> 21.5 - this file is located in neither of these installations - or at 
> least I could not find it...).

It was supposed to be part of the LucidDB installation, but...whoops, 
nothing under the ext directory is currently getting included.

For now, I've packed it up separately and you can download it from this 
link:

http://pub.eigenbase.org/mediawiki/upload/d/d9/Luciddb-monitor.tar.bz2

I've also updated the wiki page.  I'll fix the distribution problem for 
the next release.

JVS

[luciddb-users] LucidDbMonitor

[Vassil K. <vas...@gm...>]

Hi All,

Has anyone been able to install LucidDBMonitor? I am following the steps
outlined here <http://pub.eigenbase.org/wiki/LucidDbMonitor> and I'm having
all sorts of issues.
I am trying to install it on Ubuntu 8 with a GNOME desktop.

The first problem I had was that there is no Ubuntu installation package
named "moodss" so "apt-get install moodss" did not help much.
I downloaded and installed moodss from
here<http://moodss.sourceforge.net/#standalone>instead. Followed the
instructions, now I cannot start moodss.sh - something
about a missing color "white". I guess this may have something to do with
permissions, so I figured - will deal with this later...
But the worst issue is, I cannot find file "lucidDbMonitor.sh". It's not
clear from the doc page <http://pub.eigenbase.org/wiki/LucidDbMonitor> if
this file is supposed to be part of the LucidDB installation or the moodss
installation...(I have LucidDB version 0.7.4 and moodss version 21.5 - this
file is located in neither of these installations - or at least I could not
find it...).

Any help is appreciated,
Vassil

Re: [luciddb-users] Aggdesigner and Lucid

[John V. S. <js...@gm...>]

Tom Barber wrote:
> Just wondering if anyone had tried Agg designer away from localhost,
> when I do jdbc:luciddb:rmi://netl I get a nasty error:
> 
> java.net.MalformedUrlException: no protocol.....

Check the server's /etc/hosts settings:

http://pub.eigenbase.org/wiki/ClientServerLocalhost

Most likely that is the cause.  If not, could you include the full stack 
of the MalformedUrlException so we can see where it's coming from?

JVS