[Squirrel-sql-users] load only one schema in a DB2 database

[Federico Bruni]

Hi all

I've started using Squirrel to learn a bit of SQL.
I have a simple question that I can't figure out by myself. I'm connecting
to a DB2 database which has several schemas, but I'm interested in querying
only one. Currently I must specify the schema or I get an error. For
example:

SELECT column FROM schema.table

works, while:

SELECT column FROM table

doesn't.

I'd like to set a certain schema to be the default to reduce the length of
my SQL instructions.
IIUC I should change the properties of the alias. But I can load or not
load _all_ the schemas; I cannot say "load only this schema and don't load
the others". I'm missing something?

Thanks a lot in advance
Federico

[Federico Bruni]

2014-07-28 10:59 GMT+02:00 Federico Bruni <***@gmail.com>:

> Hi all
>
> I've started using Squirrel to learn a bit of SQL.
> I have a simple question that I can't figure out by myself. I'm connecting
> to a DB2 database which has several schemas, but I'm interested in querying
> only one. Currently I must specify the schema or I get an error. For
> example:
>
> SELECT column FROM schema.table
>
> works, while:
>
> SELECT column FROM table
>
> doesn't.
>
> I'd like to set a certain schema to be the default to reduce the length of
> my SQL instructions.
> IIUC I should change the properties of the alias. But I can load or not
> load _all_ the schemas; I cannot say "load only this schema and don't load
> the others". I'm missing something?
>
> Thanks a lot in advance
> Federico
>
>
I'm making a second try.
My understanding in the last paragraph above was wrong. Now I've learned
how to filter a schema: I choose the alias to connect to, then click on
"New Session Properties", open the tab Object tree and I enter my schema
name in *Schema include*.

It works, but my problem now is that there are two identical schemas in
this database. Same name, but the second is empty. Unfortunately Squirrel
is loading only the empty one, as far as I can see.
How can I work around this problem?

I'm using snapshot 20140911_2017 on debian.

Thanks
Federico

[Alex Malmyguine]

You should be able to modify your alias to specify the schema (I work with iSeries):

jdbc:as400://host.domain.corp/schema;

See if that helps. Works for me.

Thank you
Alex

From: Federico Bruni [mailto:***@gmail.com]
Sent: Friday, September 26, 2014 12:29 PM
To: squirrel-sql-***@lists.sourceforge.net
Subject: Re: [Squirrel-sql-users] load only one schema in a DB2 database

2014-07-28 10:59 GMT+02:00 Federico Bruni <***@gmail.com<mailto:***@gmail.com>>:
Hi all
I've started using Squirrel to learn a bit of SQL.
I have a simple question that I can't figure out by myself. I'm connecting to a DB2 database which has several schemas, but I'm interested in querying only one. Currently I must specify the schema or I get an error. For example:

SELECT column FROM schema.table
works, while:

SELECT column FROM table
doesn't.
I'd like to set a certain schema to be the default to reduce the length of my SQL instructions.
IIUC I should change the properties of the alias. But I can load or not load _all_ the schemas; I cannot say "load only this schema and don't load the others". I'm missing something?
Thanks a lot in advance
Federico


I'm making a second try.
My understanding in the last paragraph above was wrong. Now I've learned how to filter a schema: I choose the alias to connect to, then click on "New Session Properties", open the tab Object tree and I enter my schema name in *Schema include*.

It works, but my problem now is that there are two identical schemas in this database. Same name, but the second is empty. Unfortunately Squirrel is loading only the empty one, as far as I can see.
How can I work around this problem?

I'm using snapshot 20140911_2017 on debian.

Thanks
Federico

[Federico Bruni]

2014-09-26 18:55 GMT+02:00 Alex Malmyguine <
***@woodbridgegroup.com>:

> You should be able to modify your alias to specify the schema (I work with
> iSeries):
>
>
>
> jdbc:as400://host.domain.corp/schema;
>

Isn't that - your /schema part - the database name?
This is how I'm connecting to the database:

jdbc:db2://10.0.20.1:50000/DB_name

This database contains several schemas

[Federico Bruni]

Il 26/set/2014 18:29 "Federico Bruni" <***@gmail.com> ha scritto:
>
> 2014-07-28 10:59 GMT+02:00 Federico Bruni <***@gmail.com>:
>>
>> Hi all
>>
>> I've started using Squirrel to learn a bit of SQL.
>> I have a simple question that I can't figure out by myself. I'm
connecting to a DB2 database which has several schemas, but I'm interested
in querying only one. Currently I must specify the schema or I get an
error. For example:
>>
>> SELECT column FROM schema.table
>>
>> works, while:
>>
>> SELECT column FROM table
>>
>> doesn't.
>>
>> I'd like to set a certain schema to be the default to reduce the length
of my SQL instructions.
>> IIUC I should change the properties of the alias. But I can load or not
load _all_ the schemas; I cannot say "load only this schema and don't load
the others". I'm missing something?
>>
>> Thanks a lot in advance
>> Federico
>>
>
> I'm making a second try.
> My understanding in the last paragraph above was wrong. Now I've learned
how to filter a schema: I choose the alias to connect to, then click on
"New Session Properties", open the tab Object tree and I enter my schema
name in *Schema include*.
>
> It works, but my problem now is that there are two identical schemas in
this database. Same name, but the second is empty. Unfortunately Squirrel
is loading only the empty one, as far as I can see.
> How can I work around this problem?
>
> I'm using snapshot 20140911_2017 on debian.
>

Oops, I miss a letter, the names are different. And now the filtering is
working fine.

Back to the original question, I think should _load_ only this schema if I
want to shorten the SQL commands

[Federico Bruni]

2014-09-26 18:29 GMT+02:00 Federico Bruni <***@gmail.com>:

> I'd like to set a certain schema to be the default to reduce the length of
>> my SQL instructions.
>> IIUC I should change the properties of the alias. But I can load or not
>> load _all_ the schemas; I cannot say "load only this schema and don't load
>> the others". I'm missing something?
>>
>> Thanks a lot in advance
>> Federico
>>
>>
> I'm making a second try.
> My understanding in the last paragraph above was wrong. Now I've learned
> how to filter a schema: I choose the alias to connect to, then click on
> "New Session Properties", open the tab Object tree and I enter my schema
> name in *Schema include*.
>

It was not wrong actually, I was just confused by two different settings
preferences (alias properties and session properties).

Alias schema properties seem exactly what I want, but I confirm the problem
I described before: I can load or not load _all_ the schemas; I cannot say
"load only this schema and don't load the others".

[Samuel J Lennon]

Have you considered caching schemas in the alias properties? I'm not sure exactly of the issue, but maybe it is taking a long time to load all the schemas?

If so, I believe you can load and cache the schemas once, then change to not load schemas, so next time you start SQuirrel the Schemas will be loaded from the cache. Another variation is once you have all the schemas loaded, you can specify which to load. But you need to load all the schemas at least once to get a list you can subset.

Sam

(With OS/400 in the IBM i - iSeries - AS/400 world each library is considered a schema.)


Date: Tue, 30 Sep 2014 18:18:21 +0200
From: ***@gmail.com
To: squirrel-sql-***@lists.sourceforge.net
Subject: Re: [Squirrel-sql-users] load only one schema in a DB2 database

2014-09-26 18:29 GMT+02:00 Federico Bruni <***@gmail.com>:
I'd like to set a certain schema to be the default to reduce the length of my SQL instructions.

IIUC I should change the properties of the alias. But I can load or not load _all_ the schemas; I cannot say "load only this schema and don't load the others". I'm missing something?


Thanks a lot in advance
Federico



I'm making a second try.My understanding in the last paragraph above was wrong. Now I've learned how to filter a schema: I choose the alias to connect to, then click on "New Session Properties", open the tab Object tree and I enter my schema name in *Schema include*.
It was not wrong actually, I was just confused by two different settings preferences (alias properties and session properties).
Alias schema properties seem exactly what I want, but I confirm the problem I described before: I can load or not load _all_ the schemas; I cannot say "load only this schema and don't load the others".

[Federico Bruni]

Maybe my issue is a non-issue. I don't have any knowledge of databases, so
I may use wrong words and have wrong expectations.

Squirrel query builder is not working because it doesn't include the schema
name in the queries. This is at least my assumption because I get the same
error if I don't specify the schema in my SQL instructions.

This works:
SELECT PROGRESSIVO FROM SCHEMA-NAME.ARTICOLO

This doesn't:
SELECT PROGRESSIVO FROM ARTICOLO

Error: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704,
SQLERRMC=DB2INST1.ARTICOLO, DRIVER=3.57.82
SQLState: 42704
ErrorCode: -204
Error: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098,
SQLERRMC=2;-204;42704;DB2INST1.ARTICOLO, DRIVER=3.57.82
SQLState: 56098
ErrorCode: -727

I guess I'll have to go the hard way, use a terminal and learn the SQL
commands.

Thanks anyway Sam

2014-09-30 23:03 GMT+02:00 Samuel J Lennon <***@hotmail.com>:

> Have you considered caching schemas in the alias properties? I'm not sure
> exactly of the issue, but maybe it is taking a long time to load all the
> schemas?
>
> If so, I believe you can load and cache the schemas once, then change to
> not load schemas, so next time you start SQuirrel the Schemas will be
> loaded from the cache. Another variation is once you have all the schemas
> loaded, you can specify which to load. But you need to load all the
> schemas at least once to get a list you can subset.
>
> Sam
>
> (With OS/400 in the IBM i - iSeries - AS/400 world each library is
> considered a schema.)
>
>
> ------------------------------
> Date: Tue, 30 Sep 2014 18:18:21 +0200
> From: ***@gmail.com
> To: squirrel-sql-***@lists.sourceforge.net
> Subject: Re: [Squirrel-sql-users] load only one schema in a DB2 database
>
> 2014-09-26 18:29 GMT+02:00 Federico Bruni <***@gmail.com>:
>
> I'd like to set a certain schema to be the default to reduce the length of
> my SQL instructions.
> IIUC I should change the properties of the alias. But I can load or not
> load _all_ the schemas; I cannot say "load only this schema and don't load
> the others". I'm missing something?
>
> Thanks a lot in advance
> Federico
>
>
> I'm making a second try.
> My understanding in the last paragraph above was wrong. Now I've learned
> how to filter a schema: I choose the alias to connect to, then click on
> "New Session Properties", open the tab Object tree and I enter my schema
> name in *Schema include*.
>
>
> It was not wrong actually, I was just confused by two different settings
> preferences (alias properties and session properties).
>
> Alias schema properties seem exactly what I want, but I confirm the
> problem I described before: I can load or not load _all_ the schemas; I
> cannot say "load only this schema and don't load the others".
>
>
>
>
>
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer Achieve PCI
> DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports Are you
> Audit-Ready for PCI DSS 3.0 Compliance? Download White paper Comply to PCI
> DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
> _______________________________________________ Squirrel-sql-users mailing
> list Squirrel-sql-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>

[Alex Malmyguine]

What would happen if you added a line

SET PATH SCHEMA-NAME;

before any of your SQL statements?
That is a DB/400 way to set a schema explicitly. Not sure it would work for UDB.

Thank you!
Alex

From: Federico Bruni [mailto:***@gmail.com]
Sent: Wednesday, October 01, 2014 8:06 AM
To: Samuel J Lennon
Cc: squirrel-sql-***@lists.sourceforge.net
Subject: Re: [Squirrel-sql-users] load only one schema in a DB2 database

Maybe my issue is a non-issue. I don't have any knowledge of databases, so I may use wrong words and have wrong expectations.

Squirrel query builder is not working because it doesn't include the schema name in the queries. This is at least my assumption because I get the same error if I don't specify the schema in my SQL instructions.

This works:
SELECT PROGRESSIVO FROM SCHEMA-NAME.ARTICOLO

This doesn't:
SELECT PROGRESSIVO FROM ARTICOLO

Error: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.ARTICOLO, DRIVER=3.57.82
SQLState: 42704
ErrorCode: -204
Error: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-204;42704;DB2INST1.ARTICOLO, DRIVER=3.57.82
SQLState: 56098
ErrorCode: -727

I guess I'll have to go the hard way, use a terminal and learn the SQL commands.

Thanks anyway Sam

2014-09-30 23:03 GMT+02:00 Samuel J Lennon <***@hotmail.com<mailto:***@hotmail.com>>:
Have you considered caching schemas in the alias properties? I'm not sure exactly of the issue, but maybe it is taking a long time to load all the schemas?

If so, I believe you can load and cache the schemas once, then change to not load schemas, so next time you start SQuirrel the Schemas will be loaded from the cache. Another variation is once you have all the schemas loaded, you can specify which to load. But you need to load all the schemas at least once to get a list you can subset.

Sam

(With OS/400 in the IBM i - iSeries - AS/400 world each library is considered a schema.)

________________________________
Date: Tue, 30 Sep 2014 18:18:21 +0200
From: ***@gmail.com<mailto:***@gmail.com>
To: squirrel-sql-***@lists.sourceforge.net<mailto:squirrel-sql-***@lists.sourceforge.net>
Subject: Re: [Squirrel-sql-users] load only one schema in a DB2 database
2014-09-26 18:29 GMT+02:00 Federico Bruni <***@gmail.com<mailto:***@gmail.com>>:
I'd like to set a certain schema to be the default to reduce the length of my SQL instructions.
IIUC I should change the properties of the alias. But I can load or not load _all_ the schemas; I cannot say "load only this schema and don't load the others". I'm missing something?
Thanks a lot in advance
Federico


I'm making a second try.
My understanding in the last paragraph above was wrong. Now I've learned how to filter a schema: I choose the alias to connect to, then click on "New Session Properties", open the tab Object tree and I enter my schema name in *Schema include*.

It was not wrong actually, I was just confused by two different settings preferences (alias properties and session properties).

Alias schema properties seem exactly what I want, but I confirm the problem I described before: I can load or not load _all_ the schemas; I cannot say "load only this schema and don't load the others".





------------------------------------------------------------------------------ Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________ Squirrel-sql-users mailing list Squirrel-sql-***@lists.sourceforge.net<mailto:Squirrel-sql-***@lists.sourceforge.net> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

[Federico Bruni]

Thanks Alex!
You put me on the right track, this worked for me:

SET SCHEMA SCHEMA-NAME;

Problem solved


2014-10-01 14:57 GMT+02:00 Alex Malmyguine <
***@woodbridgegroup.com>:

> What would happen if you added a line
>
>
>
> SET PATH SCHEMA-NAME;
>
>
>
> before any of your SQL statements?
>
> That is a DB/400 way to set a schema explicitly. Not sure it would work
> for UDB.
>
>
>
> Thank you!
>
> Alex
>
>
>
> *From:* Federico Bruni [mailto:***@gmail.com]
> *Sent:* Wednesday, October 01, 2014 8:06 AM
> *To:* Samuel J Lennon
> *Cc:* squirrel-sql-***@lists.sourceforge.net
>
> *Subject:* Re: [Squirrel-sql-users] load only one schema in a DB2 database
>
>
>
> Maybe my issue is a non-issue. I don't have any knowledge of databases, so
> I may use wrong words and have wrong expectations.
>
>
>
> Squirrel query builder is not working because it doesn't include the
> schema name in the queries. This is at least my assumption because I get
> the same error if I don't specify the schema in my SQL instructions.
>
>
>
> This works:
>
> SELECT PROGRESSIVO FROM SCHEMA-NAME.ARTICOLO
>
>
>
> This doesn't:
>
> SELECT PROGRESSIVO FROM ARTICOLO
>
>
>
> Error: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704,
> SQLERRMC=DB2INST1.ARTICOLO, DRIVER=3.57.82
>
> SQLState: 42704
>
> ErrorCode: -204
>
> Error: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098,
> SQLERRMC=2;-204;42704;DB2INST1.ARTICOLO, DRIVER=3.57.82
>
> SQLState: 56098
>
> ErrorCode: -727
>
>
>
> I guess I'll have to go the hard way, use a terminal and learn the SQL
> commands.
>
>
>
> Thanks anyway Sam
>
>
>
> 2014-09-30 23:03 GMT+02:00 Samuel J Lennon <***@hotmail.com>:
>
> Have you considered caching schemas in the alias properties? I'm not
> sure exactly of the issue, but maybe it is taking a long time to load all
> the schemas?
>
> If so, I believe you can load and cache the schemas once, then change to
> not load schemas, so next time you start SQuirrel the Schemas will be
> loaded from the cache. Another variation is once you have all the schemas
> loaded, you can specify which to load. But you need to load all the
> schemas at least once to get a list you can subset.
>
> Sam
>
> (With OS/400 in the IBM i - iSeries - AS/400 world each library is
> considered a schema.)
>
> ------------------------------
>
> Date: Tue, 30 Sep 2014 18:18:21 +0200
> From: ***@gmail.com
> To: squirrel-sql-***@lists.sourceforge.net
> Subject: Re: [Squirrel-sql-users] load only one schema in a DB2 database
>
> 2014-09-26 18:29 GMT+02:00 Federico Bruni <***@gmail.com>:
>
> I'd like to set a certain schema to be the default to reduce the
> length of my SQL instructions.
>
> IIUC I should change the properties of the alias. But I can load or not
> load _all_ the schemas; I cannot say "load only this schema and don't load
> the others". I'm missing something?
>
> Thanks a lot in advance
>
> Federico
>
>
>
>
>
> I'm making a second try.
>
> My understanding in the last paragraph above was wrong. Now I've learned
> how to filter a schema: I choose the alias to connect to, then click on
> "New Session Properties", open the tab Object tree and I enter my schema
> name in *Schema include*.
>
>
> It was not wrong actually, I was just confused by two different settings
> preferences (alias properties and session properties).
>
>
>
> Alias schema properties seem exactly what I want, but I confirm the
> problem I described before: I can load or not load _all_ the schemas; I
> cannot say "load only this schema and don't load the others".
>
>
>
>
>
>
>
>
>
>
>
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer Achieve PCI
> DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports Are you
> Audit-Ready for PCI DSS 3.0 Compliance? Download White paper Comply to PCI
> DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
> _______________________________________________ Squirrel-sql-users mailing
> list Squirrel-sql-***@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>
>
>