Re: Sql problem tsm v6.22
In V6, SECOND is always going to return a value between 0 and 60; I don't' think that's what you are after. To get time differences in V6, you need to use the DB2 TIMESTAMPDIFF function. Example: select timestampdiff(8,cast( (current_timestamp-last_backup_date) as char(22))) as DBHRS from db /* Always cast as char(22); just change the constant 8 to: /* 2 seconds /* 4 minutes /* 8 hours /* 16 days /* 32 weeks /* 64 months /*128 quarters /*256 years -Original Message- From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Lee, Gary Sent: Thursday, February 16, 2012 10:00 AM To: ADSM-L@VM.MARIST.EDU Subject: [ADSM-L] Sql problem tsm v6.22 I've been trying to update an old expiration performance reporting script to run on tsm v6.2.2. I figured out that at the end of each expire run, a summary line is given with an entity of '' However I still get the error below. Anyone out there with any ideas? My db2 admin here is stumped. tsm: TSM01run expexp ANR0162W Supplemental database diagnostic information: -1:42911:-419 ([IBM][CLI Driver][DB2/LINUXX8664] SQL0419N A decimal divide operation is not valid because the result would have a negative scale. SQLSTATE=42911 ). ANR0516E SQL processing for statement select activity , cast ( ( end_time ) as date ) as Date , ( examined/cast ( SECOND ( end_time - start_time) as decimal ( 18 , 13 ) ) *3600 ) Objects Examined/Hr from summary where activity = 'EXPIRATION' and entity = '' and days ( end_time ) -days ( start_time ) = 0 failed. ANR1463E RUN: Command script EXPEXP completed in error. ANS8001I Return code 4. Gary Lee Senior System Programmer Ball State University phone: 765-285-1310
Re: SQL problem ....
Hi Kolbeinn, This was it ! Now you gave me the solution, I believe I really should have drank some more coffee ;-) Many thanks, cheers. Arnaud =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= | Arnaud Brion, Panalpina Management Ltd., IT Group | | Viaduktstrasse 42, P.O. Box, 4002 Basel - Switzerland | | Phone: +41 61 226 19 78 / Fax: +41 61 226 17 01 | =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -Original Message- From: Kolbeinn Josepsson [mailto:[EMAIL PROTECTED]] Sent: Friday, 07 February, 2003 18:23 To: [EMAIL PROTECTED] Subject: Re: SQL problem try this: select sum(physical_mb)-(select sum(physical_mb) from occupancy where stgpool_name='COPYLTO1_WIN') from occupancy where stgpool_name ='TAPELTO1_WIN' PAC Brion Arnaud Arnaud.Brion@PAN To: [EMAIL PROTECTED] ALPINA.COM cc: Sent by: ADSM: Subject: SQL problem Dist Stor Manager [EMAIL PROTECTED] .EDU 07.02.2003 10:42 Please respond to ADSM: Dist Stor Manager Hi list, Could any SQL genius explain me wat is wrong with this statement : select sum(physical_mb) from occupancy where stgpool_name='TAPELTO1_WIN' - (select sum(physical_mb) from occupancy where stgpool_name='COPYLTO1_WIN' ) When trying to use it II get following output : ANR2916E The SQL data types CHAR(12) and DECIMAL(31,2) are incompatible for operator '-'. I already tried to add dec() on both selects without achieving any valuable result ... is it a bug or do I need additional coffee to wake up my mind ? What I'm trying to achieve is to calculate the remaining amount of data to copy before a backup stgpool process finishes. Thanks in advance ! Cheers. Arnaud =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= | Arnaud Brion, Panalpina Management Ltd., IT Group | | Viaduktstrasse 42, P.O. Box, 4002 Basel - Switzerland | | Phone: +41 61 226 19 78 / Fax: +41 61 226 17 01 | =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Re: SQL problem ....
try this: select sum(physical_mb)-(select sum(physical_mb) from occupancy where stgpool_name='COPYLTO1_WIN') from occupancy where stgpool_name ='TAPELTO1_WIN' PAC Brion Arnaud Arnaud.Brion@PAN To: [EMAIL PROTECTED] ALPINA.COM cc: Sent by: ADSM: Subject: SQL problem Dist Stor Manager [EMAIL PROTECTED] .EDU 07.02.2003 10:42 Please respond to ADSM: Dist Stor Manager Hi list, Could any SQL genius explain me wat is wrong with this statement : select sum(physical_mb) from occupancy where stgpool_name='TAPELTO1_WIN' - (select sum(physical_mb) from occupancy where stgpool_name='COPYLTO1_WIN' ) When trying to use it II get following output : ANR2916E The SQL data types CHAR(12) and DECIMAL(31,2) are incompatible for operator '-'. I already tried to add dec() on both selects without achieving any valuable result ... is it a bug or do I need additional coffee to wake up my mind ? What I'm trying to achieve is to calculate the remaining amount of data to copy before a backup stgpool process finishes. Thanks in advance ! Cheers. Arnaud =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= | Arnaud Brion, Panalpina Management Ltd., IT Group | | Viaduktstrasse 42, P.O. Box, 4002 Basel - Switzerland | | Phone: +41 61 226 19 78 / Fax: +41 61 226 17 01 | =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Re: Sql problem
There is a workaround the problem but it is ugly and can work for small number of GROUP BY values. Look for my post on the thread SQL query - GROUP on derived value? from 25.09.2001. The rough idea - create records somewhere they would not be a problem. I've used CLIENT_SCHEDULES table with and empty policy domain for the task. Join the tables and group by fake records. Zlatko Krastev IT Consultant Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] Sent by:ADSM: Dist Stor Manager [EMAIL PROTECTED] To: [EMAIL PROTECTED] cc: Subject:Sql problem Hi All, I just realized I can use the summary table to get tape mount stats. However I can't get my sql to work I'm trying select hour(end_time) as Hour, count(*) from summary where activity= 'TAPE MOUNT' and date(end_time) = current date - 1 day group by hour(end_time) and TSM is complaining about the group by clause. ANR2904E Unexpected SQL key word token - 'HOUR'. | .V. nd date(end_time) =current date - 1 day group by hour(end_time) I've also tried 'group by 1' and 'group by Hour' but that doesn't work either. This is fairly standard SQL, anyone know what I'm doing wrong? Steve Harris AIX and TSM Administrator Queensland Health, Brisbane Australia ** This e-mail, including any attachments sent with it, is confidential and for the sole use of the intended recipient(s). This confidentiality is not waived or lost if you receive it and you are not the intended recipient(s), or if it is transmitted/ received in error. Any unauthorised use, alteration, disclosure, distribution or review of this e-mail is prohibited. It may be subject to a statutory duty of confidentiality if it relates to health service matters. If you are not the intended recipient(s), or if you have received this e-mail in error, you are asked to immediately notify the sender by telephone or by return e-mail. You should also delete this e-mail message and destroy any hard copies produced. **
Re: Sql problem
I tried the same thing on an Oracle database: select count(*) from sometable group by hour(sometimestampfield) I get an 'invalid column name' error. Functions that operate on fields that have a type other than 'timestamp' work as one would expect: select count(*) from sometable group by lower(somecharfield) I don't understand this either but it seems to be a general SQL restriction and not a problem with the TSM SQL interface. Alexander Steve Harris wrote: Hi All, I just realized I can use the summary table to get tape mount stats. However I can't get my sql to work I'm trying select hour(end_time) as Hour, count(*) from summary where activity= 'TAPE MOUNT' and date(end_time) = current date - 1 day group by hour(end_time) and TSM is complaining about the group by clause. ANR2904E Unexpected SQL key word token - 'HOUR'. | .V. nd date(end_time) =current date - 1 day group by hour(end_time) I've also tried 'group by 1' and 'group by Hour' but that doesn't work either. This is fairly standard SQL, anyone know what I'm doing wrong? Steve Harris AIX and TSM Administrator Queensland Health, Brisbane Australia ** This e-mail, including any attachments sent with it, is confidential and for the sole use of the intended recipient(s). This confidentiality is not waived or lost if you receive it and you are not the intended recipient(s), or if it is transmitted/ received in error. Any unauthorised use, alteration, disclosure, distribution or review of this e-mail is prohibited. It may be subject to a statutory duty of confidentiality if it relates to health service matters. If you are not the intended recipient(s), or if you have received this e-mail in error, you are asked to immediately notify the sender by telephone or by return e-mail. You should also delete this e-mail message and destroy any hard copies produced. ** -- --- Alexander Verkooijen([EMAIL PROTECTED]) Senior Systems Programmer SARA High Performance Computing
