The request for SQL-method is an Enhancement Request already in the Ingres Support System.

Just wish it get's higher on the implementation list ...



Issue 124644 SIR 119692 Requested originally by me around 1. February 2008.



The main issue about using Optimizedb is also that it's not possible to have different versions of Ingres and

Running through Ingres Net or as well create tables in OpenROAD with Statistics is your setup is Client-Server,

Or having different Platforms.

Of course you can hack a little with SSH and RSH if your permitted to access the other machine this way but

It's not a good solution.



After all the Ingres 2006-releases is a lot more sensitive about having good statistics.



Kind regards

Henrik Georg Sørensen, Organisator, Denmark

________________________________

Fra: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] På vegne af Paul Mason
Sendt: 2. juni 2009 11:38
Til: Ingres and related product discussion forum
Emne: Re: [Info-Ingres] \shell in batch mode



Marty beat me to it but the problem with running optimizedb in line is not being able to shell out it's locking. You could shell out and run optimizedb but you'd need to have committed in your parent session after creating the table, first otherwise the optimizedb from the child shell is locked by the parent.

A true in-line optimize command - i.e. SQL syntax to optimize a table within the current session and transaction would definitely be nice.

Paul

2009/6/2 Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>

Hi Lin,

You would be better off with a dbproc as shelling out to run the optimizer could expose you to lock contention problems.

IIRC the optimizedb command is a wrapper for a function...perhaps we could simply grab hold of that for a new command...sorta like modify...

I'll add it to the list.

Marty



-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of lin.a.bradbrook (AT) uk (DOT) bnpparibas.com
Sent: 02 June 2009 09:52
To: info-ingres (AT) kettleriverconsulting (DOT) com

Subject: Re: [Info-Ingres] \shell in batch mode


One of the things I would like to be able to do is to generate some optimize stats on any temporary tables I create during my SQL session.

Currently, this isn't possible.

If we could shell out in batch, we could run Optimizedb during the actual SQL and make utilising temporary tables much more efficient.

Alternatively, perhaps someone could develop a way of generating stats from within SQL - using a dbproc or even (dare I say it) a package. I guess someone would have to take on that concept as well seeing as we don't have packages either (yet)!


Lin



Internet
latepaul@gmail
.com To
info-ingres (AT) kettleriverconsulting (DOT) com
Sent by: cc
info-ingres-bo
unces@kettleri Subject
verconsulting. [Info-Ingres] \shell in batch mode
com

01/06/2009
17:54


Please respond
to
info-ingres@ke
ttleriverconsu
lting.com






Thanks to Lin for the inspiration for this.

I had a little play with \shell and soon found that whilst it drops you to a shell in interactive mode, in batch mode it runs a shell which immediately exits. What would be more useful is something we can call specific commands with and even read the output.

It turns out this is possible with a little jiggery-pokery.

\shell normally runs a shell command - however you can change this either by setting ING_SHELL or by redefining the macro {shell}.

e.g. try running this

export ING_SHELL=/bin/ls
sql imadb <<!
\shell
!

However since you can't change ING_SHELL during your SQL session you're limited to a single command.

Instead we could use {shell} - this is a terminal monitor system macro and we can re-define like so:

sql imadb | cat
INGRES TERMINAL MONITOR Copyright 2007 Ingres Corporation Ingres 2006 Release 2 Linux Version II 9.1.1 (a64.lnx/103)NPTL login Mon Jun 1 17:38:22 2009

continue
* {define; {shell}; /bin/ls}
* \v
continue
* \shell
mac.sql
shell_handler.sh
test.out
test.sql
continue
* {define; {shell}; /bin/ps}
* \v
continue
* \shell PID TTY TIME CMD 4687 pts/0 00:00:00 tm 4688 pts/0 00:00:00 cat 4707 pts/0 00:00:00 ps
30618 pts/0 00:00:00 bash
continue
* \q
Ingres 2006 Release 2 Version II 9.1.1 (a64.lnx/103)NPTL logout Mon Jun 1 17:38:59 2009

However terminal macro language is arcane and scary and not friendly to pass info back and forward to. So I decided to combine it with my favourite other technique which is using copy to create command files. In the past I've used this to generate a series of SQL commands in a table, copy it out to a file, and then run the file using \i file.sql. We can do the same thing with an SQL script.

So what I did was create a 'handler' script - this simply runs a series of commands it reads from a text file. See attached shell_handler.sh.

Then I redefined {shell} to point to my handler script. Since everything's going through the handler I could have used ING_SHELL but I prefer this as it means I can do the whole thing from the SQL script. Having said which I put the redefine in its own file - mac.sql.

{define; {shell}; /home/ingres/maspa05/shell_out/shell_handler.sh}
\v
\r

Finally I created an SQL script which includes mac.sql - thereby defining my handler script as what \shell runs. I then used the copy technique to generate my commands and to read the result.

See test.sql and test.out for this working in practice.

Obviously this is a proof-of-concept only. I'm relying on the fact that rows inserted into a heap will be retrieved in the same order and I really should create a key of some kind to force that. Also - more crucially - I'm relying on the fact that I'm the only one writing to and reading my input/output file. Two or more sessions trying to use this script would probably create a mess. That's not insoluble but it makes the scripting a little more complicated. This was an example only.

I think this is a useful little technique and I'll be writing it up for the Knowledge Base/Wiki. However I'm not saying this negates Lin's suggestion, as a more straightforward and robust approach could be incorporated into terminal monitor itself.

--
Paul Mason(See attached file: mac.sql)(See attached file: shell_handler.sh) (See attached file: test.out)(See attached file: test.sql) _______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres


--------

This communication is confidential, may be privileged and is meant only for the intended recipient. If you are not the intended recipient, please notify the sender by reply and delete the message from your system. Any unauthorised dissemination, distribution or copying hereof is prohibited.

BNP Paribas Trust Corporation UK Limited, BNP Paribas UK Limited, BNP Paribas Commodity Futures Limited, BNP Paribas Asset Management UK Limited and Investment Fund Services Limited are authorised and regulated by the Financial Services Authority.

BNP Paribas London Branch and BNP Paribas Wealth Management London Branch are authorised by the CECEI and supervised by the Commission Bancaire.

BNP Paribas London Branch is authorised and subject to limited regulation by the Financial Services Authority. Details about the extent of our authorisation and regulation by the Financial Services Authority are available from us on request. BNP Paribas is also a member of the London Stock Exchange.

BNP Paribas Wealth Management London Branch is subject to limited regulation by the Financial Services Authority. Details about the extent of our authorisation and regulation by the Financial Services Authority are available from us on request.

BNP Paribas Securities Services London Branch is authorised by the CECEI and supervised by the AMF, and subject to limited regulation by the Financial Services Authority. Details on the extent of our regulation by the Financial Services Authority are available from us on request. BNP Paribas Securities Services is also a member of the London Stock Exchange.

BNP Paribas Trust Corporation UK Limited is registered in England and Wales (registered no. 4042668) at registered office 55 Moorgate, London EC2R 6PA.

BNP Paribas UK Limited is registered in England and Wales (registered no. 1488108) at registered office 10 Harewood Avenue, London NW1 6AA.

BNP Paribas Commodity Futures Limited is registered in England and Wales (registered no. 2391477) at registered office 10 Harewood Avenue, London NW1 6AA.

BNP Paribas Asset Management UK Limited is registered in England and Wales (registered no. 2474627) at registered office 10 Harewood Avenue, London NW1 6AA.

Investment Fund Services Limited is registered in England and Wales (registered no. 6110770) at registered office 55 Moorgate, London EC2R 6PA.

BNP Paribas London Branch is registered in England and Wales (registered no. FC13447) at registered office 10 Harewood Avenue, London NW1 6AA.

BNP Paribas Wealth Management London Branch is registered in England and Wales (registered no. FC023926) at registered office 10 Harewood Avenue, London NW1 6AA.

BNP Paribas Securities Services London Branch is registered in England and Wales (registered no. BR006393) at registered office 55 Moorgate, London, EC2R 6PA.

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres




--
Paul Mason