Hello Danny, Try something like this:
select DISTINCT occ.node_name,SUM(occ.num_files)/2, SUM(occ.physical_mb)/2, occ.filespace_name from domains dom, nodes nod, occupancy occ where dom.domain_name=nod.domain_name and nod.node_name=occ.node_name and dom.domain_name='DM_PRD' GROUP BY occ.node_name,occ.filespace_name Matthew G. Leis From: Danny Schmanny <schma...@lgca.org> To: "Matthew G. Leis" <matthew.l...@usbank.com>, ADSM SubscriberList <ADSM-L@VM.MARIST.EDU> Date: 10/07/2010 07:26 AM Subject: Re: [ADSM-L] Upgrade to TSM 5.5.5 causes ANR2914E from SELECT query Greetings Matt et al: I thought I had resolved this 18 character limitation in TSM version 5.5.5 by defining the 'from' clause 'Domains' as D, 'Nodes' as N, and 'Occupancy' as O (see Revised SQL below) but the SQL statement chokes saying ANR2906E Unexpected SQL literal token - 'D'. (or Unexpected SQL literal token - 'N' or Unexpected SQL literal token - 'O'). Revised SQL: select DISTINCT O.node_name"Node Name", SUM(O.num_files)/2 AS "GrTot Files BU", SUM(O.physical_mb)/2 AS "GrTot BU (MB)", O.filespace_name"Volume" from Domains D, Nodes N, Occupancy O where D.Domain_Name=N.Domain_Name and N.Node_Name=O.Node_Name and D.Domain_Name='WESTGEAUGA' GROUP BY O.node_name,O.filespace_name Original SQL: 'select DISTINCT occupancy.node_name"Node Name", SUM(occupancy.num_files)/2 AS "Grand Total To-Date of Files Backed Up", SUM(occupancy.physical_mb)/2 AS "Grand Total To-Date of Data Backed Up (MB)", occupancy.filespace_name"Volume" from Domains, Nodes, Occupancy where Domains.Domain_Name=Nodes.Domain_Name and Nodes.Node_Name=occupancy.Node_Name and Domains.Domain_Name='WESTGEAUGA' GROUP BY occupancy.node_name,occupancy.filespace_name' I have set up SQL statements before where the ?from? clause includes an ?alias?. Anyone see how I can get this to work (until IBM fixes the 18-character limitation bug in TSM version 5.5.5? Thanks for any SQL suggestions as to what might work to get around the bug until IBM releases a 'fix'. Danny Schmanny _______________________________________ From: Matthew G. Leis [matthew.l...@usbank.com] To: ADSM SubscriberList [ads...@vm.marist.edu] CC: Subject: Re: [ADSM-L] Upgrade to TSM 5.5.5 causes ANR2914E from SELECT query Time: 10/5/2010 at 11:25AM I opened a case with IBM, here are the results: We are encountering the following APAR. IC71586: ANR2914E SQL IDENTIFIER TOKEN EXCEEDS 18 CHARACTERS AFTER UPDATETO TIVOLI STORAGE MANAGER SERVER V5.5.5 https://www-304.ibm.com/support/entdocview.wss?uid=swg1IC71586&myns=swgt iv&mynp=OCSSGSG7&mync=R At this time a fix is being developed and the local fix is "Modify select statements to have a column identifier shorter than 18 characters until a fix is available." For notification of when the fix is available. In the below URL, there is a section to subscribe to the APAR. https://www-304.ibm.com/support/entdocview.wss?uid=swg1IC71586&myns=swgt iv&mynp=OCSSGSG7&mync=R Regards, Matthew G. Leis From: Danny Schmanny <schma...@lgca.org> To: ADSM-L@VM.MARIST.EDU Date: 10/05/2010 09:32 AM Subject: Re: [ADSM-L] Upgrade to TSM 5.5.5 causes ANR2914E from SELECT query Sent by: "ADSM: Dist Stor Manager" <ADSM-L@VM.MARIST.EDU> Hi Matt: We are on TSM 5.5.5 and are getting the same error so I'm glad to hear I'm not alone (I was wondering why the reports started erroring out like that). Below is the error I am seeing: Session established with server STORSERVER: Windows Server Version 5, Release 5, Level 5.0 Server date/time: 10/04/2010 07:31:26 Last access: 10/04/2010 07:30:47 ANS8000I Server command: 'select DISTINCT occupancy.node_name"Node Name", SUM(occupancy.num_files)/2 AS "Grand Total To-Date of District Server Files Backed Up", SUM(occupancy.physical_mb)/2 AS "Grand Total To-Date of District Server Data Backed Up (MB)", occupancy.filespace_name"Volume" from Domains, Nodes, Occupancy where Domains.Domain_Name=Nodes.Domain_Name and NodesNode_Name=occupancy.Node_Name and Domains.Domain_Name='WESTGEAUGA' GROUP BY occupancy.node_name,occupancy.filespace_name' ANR2914E SQL identifier token 'OCCUPANCY.NODE_NAME' is too long; name or component exceeds 18 characters. | ..............V.............................................. select DISTINCT occupancy.node_name"Node Name", SUM(occupancy.n Regards, Danny Schmanny _______________________________________ From: Matthew G. Leis [matthew.l...@usbank.com] To: ADSM SubscriberList [ads...@vm.marist.edu] CC: Subject: [ADSM-L] Upgrade to TSM 5.5.5 causes ANR2914E from SELECT query Time: 10/5/2010 at 9:58AM After upgrading to TSM 5.5.5, we are unable to generate reports that rely on complex select queries to the database. This error is generated: ANR2914E SQL IDENTIFIER TOKEN NAME OR COMPONENT EXCEEDS 18 CHARACTERS Here is an example from EMC DPA: WARN 27564.27574 20101005:062907 clctr.mod.tsm.lib - getInfoFromSummary(): tsmGetCommandResults(summary) query SELECT summary.start_time, summary.end_time, summary.activity, summary.number, summary.entity, summary.schedule_name, summary.examined, summary.affected, summary.failed, summary.bytes, summary.successful FROM summary WHERE summary.end_time>='10/04/2010 04:32:32' AND summary.end_time<'10/05/2010 06:29:01' returned NULL WARN 27564.27574 20101005:062907 clctr.mod.tsm.job - _tsmGetCompletedJobsFromSummary(): failed to get summary information for tsmep13 WARN 27564.27574 20101005:062907 clctr.mod.tsm.job - processBackupJobEntries(): failed to get current jobs for tsmep13 WARN 27564.27572 20101005:065918 clctr.mod.tsm.lib - _tsmQuery(): error returned from server tsmep13: ANR2914E SQL identifier token 'SUMMARY.SCHEDULE_NAME' is too long; name or component exceeds 18 characters. Has anyone else experienced this issue? Thanks for any help, Matt. U.S. BANCORP made the following annotations --------------------------------------------------------------------- Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. --------------------------------------------------------------------- Thanks. Danny Schmanny U.S. BANCORP made the following annotations --------------------------------------------------------------------- Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. --------------------------------------------------------------------- Thanks. Danny Schmanny U.S. BANCORP made the following annotations --------------------------------------------------------------------- Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation. ---------------------------------------------------------------------
