Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE
Well Todd, This is the TSM default. In order to get the output in the required format you need to use the -outfile option. This way you will get the output as if you are at the admin command line. Like this: dsmadmc -id= -pa= -outfile=path_to_outfile your select goes here Ilja G. Coolen _ ABP / USZO CIS / BS / TB / Storage Management Telefoon : +31(0)45 579 7938 Fax : +31(0)45 579 3990 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Intranet : Storage Web http://intranet/cis_bstb/html_content/sm/index_sm.htm _ - Everybody has a photographic memory, some just don't have film. - -Oorspronkelijk bericht- Van: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED]]Namens Pucky, Todd M. Verzonden: maandag 3 december 2001 13:44 Aan: [EMAIL PROTECTED] Onderwerp: SQL Formatting on AIX scripts - SQLDISPLAYMODE TSM Server: AIX 4.3.3 I have seen this issue addressed in the adsm archives, but never a clear answer on how to solve it. I plan to use a script in a cron job on AIX, but I need to have SQLDISPLAYMODE set to WIDE. Here is the part of my script where I attempt to set the SQLDISPLAYMODE to WIDE and issue the query. dsmadmc -id=xx -pas=xx set sqldisplaymode wide dsmadmc -id=xx -pas=xx select events.node_name, events.s tatus, nodes.contact from events, nodes where events.domain_name='SERVER' and ev ents.schedule_name='SERVER_0300_0500' and (events.status='Missed' or events.stat us='Failed') and events.node_name=nodes.node_name $eventtmp I have no problems when I issue these commands from the Admin Command Line, but through the AIX script I always get SQL format of NARROW in the results of my query. Any suggestions on how to get the SQLDISPLAYMODE WIDE command to work in the AIX script would be greatly appreciated. Thanks Todd M. Pucky The Timken Company Global Information Systems ** This message and any attachments are intended for the individual or entity named above. If you are not the intended recipient, please do not read, copy, use or disclose this communication to others; also please notify the sender by replying to this message, and then delete it from your system. The Timken Company **
Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE
The SET SQLDISPLAYMODE command only holds for a given session. Thus when the admin session from which you issued that command ends, the next admin session that issues the SELECT command uses the default SQLDISPLAYMODE. How about combining your SET SQLDISPLAYMODE and SELECT commands into a single admin macro file, then using the admin MACRO command to process the macro? (See the Admin Reference for info about the MACRO command.) Another formatting issue that you might run across is the case where the command line displays the information in list format (each column of output is displayed on a single line) when you use the interactive admin CLI, but when the output is redirected, the information is displayed in table (row-column) format. For this case, have a look at the -DISPLAYMODE option. You can find more about this option in the Admin Reference. Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. Pucky, Todd M. [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager [EMAIL PROTECTED] 12/03/2001 05:44 Please respond to ADSM: Dist Stor Manager To: [EMAIL PROTECTED] cc: Subject:SQL Formatting on AIX scripts - SQLDISPLAYMODE TSM Server: AIX 4.3.3 I have seen this issue addressed in the adsm archives, but never a clear answer on how to solve it. I plan to use a script in a cron job on AIX, but I need to have SQLDISPLAYMODE set to WIDE. Here is the part of my script where I attempt to set the SQLDISPLAYMODE to WIDE and issue the query. dsmadmc -id=xx -pas=xx set sqldisplaymode wide dsmadmc -id=xx -pas=xx select events.node_name, events.s tatus, nodes.contact from events, nodes where events.domain_name='SERVER' and ev ents.schedule_name='SERVER_0300_0500' and (events.status='Missed' or events.stat us='Failed') and events.node_name=nodes.node_name $eventtmp I have no problems when I issue these commands from the Admin Command Line, but through the AIX script I always get SQL format of NARROW in the results of my query. Any suggestions on how to get the SQLDISPLAYMODE WIDE command to work in the AIX script would be greatly appreciated. Thanks Todd M. Pucky The Timken Company Global Information Systems ** This message and any attachments are intended for the individual or entity named above. If you are not the intended recipient, please do not read, copy, use or disclose this communication to others; also please notify the sender by replying to this message, and then delete it from your system. The Timken Company **
Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE
Todd, here's another way around the problem: Add -commadelimited as a parm after -pa=. That causes the output to come out in a wide string, with the fields separated by commas, no wrapping. Since you are doing this from AIX, it's very easy to handle that output in the shell script. You could, for example, pipe the dsmadmc command to a tr command that just translates the commas back to blanks. -Original Message- From: Alexandre Bicas To: [EMAIL PROTECTED] Sent: 12/3/01 8:41 AM Subject: RES: SQL Formatting on AIX scripts - SQLDISPLAYMODE Todd, This problem occurs because SQLDISPLAYMODE is a parameter set only for the current session. Each time you issue dsmadmc comands you are invoking a new session and SQLDISPLAYMODE is reset to NARROW. You can check this issuing Q SQLSESSION after setting SQLDISPLAYMODE. I would create a Server Command Script inside TSM which would have the SQLDISPLAYMODE and the SELECT clauses into it. Once this script is created, you just have to run it from your cron as you do it today (dsmadmc -id=xxx -pa=yyy run my_script_name) Good luck ! Alexandre Bicas Caldeira - [EMAIL PROTECTED] Tivoli Certified Specialist - Tivoli Storage Manager Service IT Solutions - http://www.service.com.br Sao Paulo:(11) 3054-1400 Rua Vicente Pinzon, 144, cj 112 CEP 04547-130 - Sco Paulo - SP Porto Alegre: (51) 212-3666 Curitiba: (41) 363-5586 -Mensagem original- De: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED]]Em nome de Pucky, Todd M. Enviada em: segunda-feira, 3 de dezembro de 2001 09:44 Para: [EMAIL PROTECTED] Assunto: SQL Formatting on AIX scripts - SQLDISPLAYMODE TSM Server: AIX 4.3.3 I have seen this issue addressed in the adsm archives, but never a clear answer on how to solve it. I plan to use a script in a cron job on AIX, but I need to have SQLDISPLAYMODE set to WIDE. Here is the part of my script where I attempt to set the SQLDISPLAYMODE to WIDE and issue the query. dsmadmc -id=xx -pas=xx set sqldisplaymode wide dsmadmc -id=xx -pas=xx select events.node_name, events.s tatus, nodes.contact from events, nodes where events.domain_name='SERVER' and ev ents.schedule_name='SERVER_0300_0500' and (events.status='Missed' or events.stat us='Failed') and events.node_name=nodes.node_name $eventtmp I have no problems when I issue these commands from the Admin Command Line, but through the AIX script I always get SQL format of NARROW in the results of my query. Any suggestions on how to get the SQLDISPLAYMODE WIDE command to work in the AIX script would be greatly appreciated. Thanks Todd M. Pucky The Timken Company Global Information Systems ** This message and any attachments are intended for the individual or entity named above. If you are not the intended recipient, please do not read, copy, use or disclose this communication to others; also please notify the sender by replying to this message, and then delete it from your system. The Timken Company **
Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE
Within the select statement itself you can specify a header in place of the default and make it the width desired. IE: select VOLUME_NAME,STGPOOL_NAME--STORAGE`_POOL_NAME-,LAST_WRITE_DATE -MM-DD_HH-MM-SS_--- from volumes. The parameter in quotes will be sent as the header, with that width. Hope this helps. Denny P. -Original Message- From: Andrew Raibeck [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 9:26 AM To: [EMAIL PROTECTED] Subject: Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE The SET SQLDISPLAYMODE command only holds for a given session. Thus when the admin session from which you issued that command ends, the next admin session that issues the SELECT command uses the default SQLDISPLAYMODE. How about combining your SET SQLDISPLAYMODE and SELECT commands into a single admin macro file, then using the admin MACRO command to process the macro? (See the Admin Reference for info about the MACRO command.) Another formatting issue that you might run across is the case where the command line displays the information in list format (each column of output is displayed on a single line) when you use the interactive admin CLI, but when the output is redirected, the information is displayed in table (row-column) format. For this case, have a look at the -DISPLAYMODE option. You can find more about this option in the Admin Reference. Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. Pucky, Todd M. [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager [EMAIL PROTECTED] 12/03/2001 05:44 Please respond to ADSM: Dist Stor Manager To: [EMAIL PROTECTED] cc: Subject:SQL Formatting on AIX scripts - SQLDISPLAYMODE TSM Server: AIX 4.3.3 I have seen this issue addressed in the adsm archives, but never a clear answer on how to solve it. I plan to use a script in a cron job on AIX, but I need to have SQLDISPLAYMODE set to WIDE. Here is the part of my script where I attempt to set the SQLDISPLAYMODE to WIDE and issue the query. dsmadmc -id=xx -pas=xx set sqldisplaymode wide dsmadmc -id=xx -pas=xx select events.node_name, events.s tatus, nodes.contact from events, nodes where events.domain_name='SERVER' and ev ents.schedule_name='SERVER_0300_0500' and (events.status='Missed' or events.stat us='Failed') and events.node_name=nodes.node_name $eventtmp I have no problems when I issue these commands from the Admin Command Line, but through the AIX script I always get SQL format of NARROW in the results of my query. Any suggestions on how to get the SQLDISPLAYMODE WIDE command to work in the AIX script would be greatly appreciated. Thanks Todd M. Pucky The Timken Company Global Information Systems ** This message and any attachments are intended for the individual or entity named above. If you are not the intended recipient, please do not read, copy, use or disclose this communication to others; also please notify the sender by replying to this message, and then delete it from your system. The Timken Company ** This message was scanned for viruses on behalf of sender. This message was scanned for viruses on behalf of sender.
Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE
Interesting ... maybe even marvelous BUT ... Can you tell us how to SHRINK the headers??? In specifying for volume_name I still get the - underlines. Like: --STORAGE`_POOL_NAME- -- - 20 TAPEPOOL SIGH! ... joe.f. Joseph A Faracchio, Systems Programmer, UC Berkeley Private mail on any topic should be directed to : [EMAIL PROTECTED] (510)642-7638 (w) (209)483-JOEF (M) 5633 On Mon, 3 Dec 2001, Poehlman, James wrote: Within the select statement itself you can specify a header in place of the default and make it the width desired. IE: select VOLUME_NAME,STGPOOL_NAME--STORAGE`_POOL_NAME-,LAST_WRITE_DATE -MM-DD_HH-MM-SS_--- from volumes. The parameter in quotes will be sent as the header, with that width. Hope this helps. Denny P. -Original Message- From: Andrew Raibeck [mailto:[EMAIL PROTECTED]] Sent: Monday, December 03, 2001 9:26 AM To: [EMAIL PROTECTED] Subject: Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE The SET SQLDISPLAYMODE command only holds for a given session. Thus when the admin session from which you issued that command ends, the next admin session that issues the SELECT command uses the default SQLDISPLAYMODE. How about combining your SET SQLDISPLAYMODE and SELECT commands into a single admin macro file, then using the admin MACRO command to process the macro? (See the Admin Reference for info about the MACRO command.) Another formatting issue that you might run across is the case where the command line displays the information in list format (each column of output is displayed on a single line) when you use the interactive admin CLI, but when the output is redirected, the information is displayed in table (row-column) format. For this case, have a look at the -DISPLAYMODE option. You can find more about this option in the Admin Reference. Regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS Internet e-mail: [EMAIL PROTECTED] The only dumb question is the one that goes unasked. The command line is your friend. Good enough is the enemy of excellence. Pucky, Todd M. [EMAIL PROTECTED] Sent by: ADSM: Dist Stor Manager [EMAIL PROTECTED] 12/03/2001 05:44 Please respond to ADSM: Dist Stor Manager To: [EMAIL PROTECTED] cc: Subject:SQL Formatting on AIX scripts - SQLDISPLAYMODE TSM Server: AIX 4.3.3 I have seen this issue addressed in the adsm archives, but never a clear answer on how to solve it. I plan to use a script in a cron job on AIX, but I need to have SQLDISPLAYMODE set to WIDE. Here is the part of my script where I attempt to set the SQLDISPLAYMODE to WIDE and issue the query. dsmadmc -id=xx -pas=xx set sqldisplaymode wide dsmadmc -id=xx -pas=xx select events.node_name, events.s tatus, nodes.contact from events, nodes where events.domain_name='SERVER' and ev ents.schedule_name='SERVER_0300_0500' and (events.status='Missed' or events.stat us='Failed') and events.node_name=nodes.node_name $eventtmp I have no problems when I issue these commands from the Admin Command Line, but through the AIX script I always get SQL format of NARROW in the results of my query. Any suggestions on how to get the SQLDISPLAYMODE WIDE command to work in the AIX script would be greatly appreciated. Thanks Todd M. Pucky The Timken Company Global Information Systems ** This message and any attachments are intended for the individual or entity named above. If you are not the intended recipient, please do not read, copy, use or disclose this communication to others; also please notify the sender by replying to this message, and then delete it from your system. The Timken Company ** This message was scanned for viruses on behalf of sender. This message was scanned for viruses on behalf of sender.
Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE
Joe - you can get shorter headers by selecting functions on the columns, for example - select left(volume_name,6) as volume, left(stgpool_name,10) as stgpool - from volumes - where stgpool_name = '9840APOOL' - and status = 'FULL' - and date(last_write_date) = current date - 7 days Which produces this in dsmadmc -- adsm select left(volume_name,6) as volume, left(stgpool_name,10) as stgpool - cont from volumes - cont where stgpool_name = '9840APOOL' - cont and status = 'FULL' - cont and date(last_write_date) = current date - 7 days VOLUME STGPOOL -- -- 92 9840APOOL 900067 9840APOOL 900176 9840APOOL 900444 9840APOOL 900584 9840APOOL 902090 9840APOOL 902281 9840APOOL 902385 9840APOOL 902387 9840APOOL 902453 9840APOOL 902460 9840APOOL 902465 9840APOOL 902469 9840APOOL 902478 9840APOOL 902500 9840APOOL 902511 9840APOOL 902522 9840APOOL 902543 9840APOOL 902545 9840APOOL 902549 9840APOOL adsm Hope this helps, -- -- Bill Colwell C. S. Draper Lab Cambridge, Ma. [EMAIL PROTECTED] -- In [EMAIL PROTECTED], on 12/03/01 at 03:49 PM, Joe Faracchio [EMAIL PROTECTED] said: Interesting ... maybe even marvelous BUT ... Can you tell us how to SHRINK the headers??? In specifying for volume_name I still get the - underlines. Like: --STORAGE`_POOL_NAME- -- - 20 TAPEPOOL SIGH! ... joe.f. Joseph A Faracchio, Systems Programmer, UC Berkeley Private mail on any topic should be directed to : [EMAIL PROTECTED] (510)642-7638 (w) (209)483-JOEF (M) 5633 On Mon, 3 Dec 2001, Poehlman, James wrote: Within the select statement itself you can specify a header in place of the default and make it the width desired. IE: select VOLUME_NAME,STGPOOL_NAME--STORAGE`_POOL_NAME-,LAST_WRITE_DATE -MM-DD_HH-MM-SS_--- from volumes. The parameter in quotes will be sent as the header, with that width. Hope this helps. Denny P.
