Page 1 of 4 123 ... LastLast
Results 1 to 15 of 47
  1. #1
    Join Date
    Oct 2010
    Posts
    27

    Unanswered: SQL Datetime Error

    I am running the following script on Aix against a v6.2 TSM server which is now using DB2 database. We used to run this for v5 TSM which used the proprietary SQL database.

    #!/usr/bin/ksh
    #
    # Check the sys file and the stanza
    #
    # v 2.1 - June 2, 2004 - Stand alone version
    #
    set -x
    #############################
    # Setup
    #############################
    sysFile=/usr/tivoli/tsm/client/ba/bin64/dsm.sys
    outFile=/usr/tivoli/accounting/DAILY_SUMMARY.out
    installPath=`dirname $_`

    #############################
    # Functions
    #############################
    getOpts () {

    parmerr="no"

    while getopts s:i:m:w:d: name
    do
    case $name in
    s) serverName="$OPTARG";;
    i) id="$OPTARG";;
    p) pw="$OPTARG";;
    m) mailTo="$OPTARG";;
    w) window="$OPTARG";;
    d) daysBack="$OPTARG";;
    esac
    done

    if [[ -z $serverName ]] ; then
    logIt "The serverName is null. (-s 'server_name1,server_name2,...')"
    fi
    if [[ -z $id ]] ; then
    logIt "There is no TSM admin ID specified (-i admin_name)"
    parmerr="yes"
    fi
    if [[ -z $pw ]] ; then
    logIt "There is no TSM admin password specified (-p admin_pw)"
    parmerr="yes"
    fi
    if [[ -z $mailTo ]] ; then
    logIt "There is no e-mail recipients (-m mail1@some.com,mail2@someother.com,...)"
    parmerr="yes"
    fi

    if [[ -z $daysBack ]] ; then
    logIt "Defaulting to last night (-d is null)"
    daysBack=0;
    fi

    if [[ -z $window ]] ; then
    logIt "There is no backup window specified will (cross midnight) (e.g. -w 21:00,05:00)"
    parmerr="yes"
    else
    start=`echo $window | awk -F, '{print $1}'`
    end=`echo $window | awk -F, '{print $2}'`
    logIt "Start Time=$start, End=$end"
    fi

    if [[ $parmerr = "yes" ]] ; then
    exit 1
    fi

    emailTitle="TSM Daily Summary for Servers: $serverName"
    }

    runReport () {

    #
    # Be sure the server staza and dsm.sys file exist
    #
    servs=`echo $serverName | awk '{gsub(/,/," "); print $0}'`
    for i in $servs ; do
    if [[ -f $sysFile ]] ; then
    egrep -i "^se.* $i" /usr/tivoli/tsm/client/ba/bin64/dsm.sys > /dev/null 2>&1
    if [[ $? -ne 0 ]] ; then
    logIt "The stanza $i does not exist. Add the stanza to $sysFile."
    exit 1
    else
    logIt "Will Report on server: $i"
    runSQL $i
    fi
    else
    logIt "The system options file cannot be found. " \
    "Expected file name: ${sysFile}"
    exit 1
    fi
    done
    }

    logIt () {
    dateTime
    echo "${timeStamp} - $1"
    return
    }

    dateTime () {
    timeStamp=$(date +'%Y-%m-%d %H:%M:%S')
    theDate=$(date +'%m-%d-%Y')
    theTime=$(date +'%H:%M:%S')
    return
    }

    runSQL () {

    /usr/bin/dsmadmc -noconfirm -se=$1 -id=$id -pa=$pw -comma << EOF >> $outFile
    select 'FLAG', -
    'SERVER', -
    'NODE_NAME', -
    'START_DATE', -
    'START_TIME', -
    'END_DATE', -
    'END_TIME', -
    'TOTAL_GB', -
    'MEDIAW_MINS', -
    'DUR_MINS', -
    'MB_SEC', -
    'IDLE_MIN', -
    'SESSIONS', -
    'EXAMINED', -
    'AFFECTED', -
    'FAILED', -
    'SUCCESS', -
    'ACTIVITY', -
    'COMM_WAIT_MINS' -
    from status
    select '--', -
    (select server_name from status) as TSM_SERVER, -
    cast(entity as char(40)) as NODE_NAME, -
    date(min(start_time)) as START_DATE, -
    time(min(start_time)) as START_TIME, -
    date(max(end_time)) as END_DATE, -
    time(max(end_time)) as END_TIME, -
    cast(sum(bytes)/1024/1024 as decimal(10,0)) as TOTAL_MB, -
    sum(mediaw)/60 as MEDIAW_MINS, -
    (max(end_time)-min(start_time))minutes as DUR_MINS, -
    cast(sum(bytes) / cast((max(end_time)-min(start_time))seconds as decimal) as decimal)/1024/1024 as MB_SEC, -
    sum(idle)/60 as IDLE_MIN, -
    count(*) as SESSIONS, -
    sum(examined) as EXAMINED, -
    sum(affected) as AFFECTED, -
    sum(failed) as FAILED, -
    min(successful) as SUCCESS, -
    activity, -
    sum(comm_wait)/60 as COMM_WAIT_MINS -
    from summary -
    where ( start_time>=timestamp(char(date(current_timestamp)-($daysBack+1)day)||' ${start}') and -
    start_time<=timestamp(char(date(current_timestamp)-($daysBack)day)||' ${end}') ) and -
    activity in ('BACKUP','ARCHIVE','RESTORE','RETRIEVE') -
    group by entity,activity -
    order by 8 desc
    EOF
    }



    #############################
    # Start Program Flow
    #############################
    getOpts $*
    logIt "Install Path=${installPath}"
    if [[ -f $outFile ]] ; then
    logIt "Removing old temp output file."
    rm $outFile
    fi
    runReport "$serverName"
    egrep '^--' $outFile | \
    awk -F, -f $installPath/daily_summary.awk | \
    uuencode DailySummary.txt | \
    mail -s "$emailTitle" ${mailTo}
    exit 0

    When I run this now I get the following error:

    IBM Tivoli Storage Manager
    Command Line Administrative Interface - Version 6, Release 2, Level 1.0
    (c) Copyright by IBM Corporation and other(s) 1990, 2010. All Rights Reserved.

    Session established with server AXTSM01: AIX
    Server Version 6, Release 2, Level 1.0
    Server date/time: 10/07/10 12:35:30 Last access: 10/07/10 12:11:10

    FLAG,SERVER,NODE_NAME,START_DATE,START_TIME,END_DA TE,END_TIME,TOTAL_GB,MEDIAW_MINS,DUR_MINS,MB_SEC,I DLE_MIN,SESSIONS,EXAMINED,AFFECTED,FAILED,SUCCESS, ACTIVITY,COMM_WAIT_MINS
    ANR0162W Supplemental database diagnostic information: -1:22007:-180 ([IBM][CLI Driver][DB2/AIX64] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
    ).
    ANR0516E SQL processing for statement select '--' , ( select server_name from status ) as TSM_SERVER , cast ( entity as char ( 40 ) ) as NODE_NAME , date ( min ( start_time ) ) as START_DATE , time ( min ( start_time ) ) as START_TIME , date ( max ( end_time ) ) as END_DATE , time ( max ( end_time ) ) as END_TIME , cast ( sum ( bytes ) /1024/1024 as decimal ( 10 , 0 ) ) as TOTAL_MB , sum ( mediaw ) /60 as MEDIAW_MINS , MINUTE ( max ( end_time ) -min ( start_time )) as DUR_MINS , cast ( sum ( bytes ) / cast ( SECOND ( max ( end_time ) -min ( start_time )) as decimal ) as decimal ) /1024/1024 as MB_SEC , sum ( idle ) /60 as IDLE_MIN , count ( * ) as SESSIONS , sum ( examined ) as EXAMINED , sum ( affected ) as AFFECTED , sum ( failed ) as FAILED , min ( successful ) as SUCCESS , activity , sum ( comm_wait ) /60 as COMM_WAIT_MINS from summary where ( start_time >= timestamp ( char ( date ( current_timestamp ) - ( 0+1 ) day ) || ' 21:00' ) and start_time <= timestamp ( char ( date ( current_timestamp ) - ( 0 )
    day ) || ' 06:00' ) ) and activity in ( 'BACKUP' , 'ARCHIVE' , 'RESTORE' , 'RETRIEVE' ) group by entity , activity order by 8 desc failed.
    ANS8001I Return code 3.

    ANS8002I Highest return code was 3.

    I know I need to change the date format somehow, but not sure what I exactly I need to change.

    Any help would be greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Moved from Microsoft SQL Server to DB2. You'll get better answers for DB2 questions here!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2010
    Posts
    14

    Try this please..

    I think i found the problem, once i saw the entire script - ignore my post on the earlier thread.

    You should probably change the way the timestamp is formulated - depending on your system, casting to a date might be generating it to the mm/dd/yyyy or dd/mm/yyyy format - which when provided to the timestamp function, will throw an error.

    C:\Users\Andy>db2 "values timestamp(char(date(current_timestamp))||' 00:00:00.0')"

    1
    --------------------------
    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007



    Use something like this.. Providing ISO as the second argument to the CHAR function - it should work then.

    C:\Users\Andy>db2 "values timestamp(char(date(current_timestamp),ISO)||' 00:00:00.0')"

    1
    --------------------------
    2010-10-09-00.00.00.000000

    1 record(s) selected.



    In your case, it should be like this - include the seconds component for time as well:
    where ( start_time >= timestamp ( char ( (date ( current_timestamp ) - ( 0+1 ) day),ISO ) || ' 21:00:00' ) and start_time <= timestamp ( char ( (date ( current_timestamp ) - ( 0 )
    day),ISO ) || ' 06:00:00' ) )


    Let me know how it goes.
    Last edited by Anand.Kaushal; 10-09-10 at 08:11.

  4. #4
    Join Date
    Oct 2010
    Posts
    27
    Anaud, I got the same error. Do I need to change the format in the timeStamp routine?

    IBM Tivoli Storage Manager
    Command Line Administrative Interface - Version 6, Release 2, Level 1.0
    (c) Copyright by IBM Corporation and other(s) 1990, 2010. All Rights Reserved.

    Session established with server AXTSM01: AIX
    Server Version 6, Release 2, Level 1.0
    Server date/time: 10/09/10 08:13:01 Last access: 10/09/10 07:30:00

    FLAG,SERVER,NODE_NAME,START_DATE,START_TIME,END_DA TE,END_TIME,TOTAL_GB,MEDIAW_MINS,DUR_MINS,MB_SEC,I DLE_MIN,SESSIONS,EXAMINED,AFFECTED,FAILED,SUCCESS, ACTIVITY,COMM_WAIT_MINS
    ANR0162W Supplemental database diagnostic information: -1:22007:-180 ([IBM][CLI Driver][DB2/AIX64] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
    ).
    ANR0516E SQL processing for statement select '--' , ( select server_name from status ) as TSM_SERVER , cast ( entity as char ( 40 ) ) as NODE_NAME , date ( min ( start_time ) ) as START_DATE , time ( min ( start_time ) ) as START_TIME , date ( max ( end_time ) ) as END_DATE , time ( max ( end_time ) ) as END_TIME , cast ( sum ( bytes ) /1024/1024 as decimal ( 10 , 0 ) ) as TOTAL_MB , sum ( mediaw ) /60 as MEDIAW_MINS , MINUTE ( max ( end_time ) -min ( start_time )) as DUR_MINS , cast ( sum ( bytes ) / cast ( SECOND ( max ( end_time ) -min ( start_time )) as decimal ) as decimal ) /1024/1024 as MB_SEC , sum ( idle ) /60 as IDLE_MIN , count ( * ) as SESSIONS , sum ( examined ) as EXAMINED , sum ( affected ) as AFFECTED , sum ( failed ) as FAILED , min ( successful ) as SUCCESS , activity , sum ( comm_wait ) /60 as COMM_WAIT_MINS from summary where ( start_time >= timestamp ( char ( ( date ( current_timestamp ) - ( 0+1 ) day ) , ISO ) || ' 18:00' ) and start_time <= timestamp ( char ( ( date ( current_timestamp
    ) - ( 0 ) day ) , ISO ) || ' ' ) ) and activity in ( 'BACKUP' , 'ARCHIVE' , 'RESTORE' , 'RETRIEVE' ) group by entity , activity order by 8 desc failed.
    ANS8001I Return code 3.

    ANS8002I Highest return code was 3.

  5. #5
    Join Date
    Oct 2010
    Posts
    14

    check the end time part

    The time component is blank in the second predicate - that's the issue i suppose

    start_time <= timestamp ( char ( ( date ( current_timestamp
    ) - ( 0 ) day ) , ISO ) || ' ' ) )

  6. #6
    Join Date
    Oct 2010
    Posts
    27
    Quote Originally Posted by Anand.Kaushal View Post
    The time component is blank in the second predicate - that's the issue i suppose

    start_time <= timestamp ( char ( ( date ( current_timestamp
    ) - ( 0 ) day ) , ISO ) || ' ' ) )
    When you say blank, are you referring to the zero? What I'm doing is querying a time range previous to midnight. (i.e 18:00 to 06:00)

    In order to do that I'm adding 1 day in the first predicate.

  7. #7
    Join Date
    Oct 2010
    Posts
    14

    i mean the quotes ''

    The zero is fine.. it's the quotes... put in 00:00:00 if endtime is blank/null..

    Also, don't forget to add the seconds component to the time for starttime as well..

    That part you are appending after char should be hh:mm:ss format

  8. #8
    Join Date
    Oct 2010
    Posts
    27
    Now I get this error:

    IBM Tivoli Storage Manager
    Command Line Administrative Interface - Version 6, Release 2, Level 1.0
    (c) Copyright by IBM Corporation and other(s) 1990, 2010. All Rights Reserved.

    Session established with server AXTSM01: AIX
    Server Version 6, Release 2, Level 1.0
    Server date/time: 10/09/10 08:53:38 Last access: 10/09/10 08:13:02

    FLAG,SERVER,NODE_NAME,START_DATE,START_TIME,END_DA TE,END_TIME,TOTAL_GB,MEDIAW_MINS,DUR_MINS,MB_SEC,I DLE_MIN,SESSIONS,EXAMINED,AFFECTED,FAILED,SUCCESS, ACTIVITY,COMM_WAIT_MINS
    ANR0162W Supplemental database diagnostic information: -1:22003:-413 ([IBM][CLI Driver][DB2/AIX64] SQL0413N Overflow occurred during numeric data type conversion. SQLSTATE=22003
    ).
    ANR0516E SQL processing for statement select '--' , ( select server_name from status ) as TSM_SERVER , cast ( entity as char ( 40 ) ) as NODE_NAME , date ( min ( start_time ) ) as START_DATE , time ( min ( start_time ) ) as START_TIME , date ( max ( end_time ) ) as END_DATE , time ( max ( end_time ) ) as END_TIME , cast ( sum ( bytes ) /1024/1024 as decimal ( 10 , 0 ) ) as TOTAL_MB , sum ( mediaw ) /60 as MEDIAW_MINS , MINUTE ( max ( end_time ) -min ( start_time )) as DUR_MINS , cast ( sum ( bytes ) / cast ( SECOND ( max ( end_time ) -min ( start_time )) as decimal ) as decimal ) /1024/1024 as MB_SEC , sum ( idle ) /60 as IDLE_MIN , count ( * ) as SESSIONS , sum ( examined ) as EXAMINED , sum ( affected ) as AFFECTED , sum ( failed ) as FAILED , min ( successful ) as SUCCESS , activity , sum ( comm_wait ) /60 as COMM_WAIT_MINS from summary where ( start_time >= timestamp ( char ( ( date ( current_timestamp ) - ( 0+1 ) day ) , ISO ) || ' 18:00:00' ) and start_time <= timestamp ( char ( ( date (
    current_timestamp ) - ( 0 ) day ) , ISO ) || ' 06:00:00' ) ) and activity in ( 'BACKUP' , 'ARCHIVE' , 'RESTORE' , 'RETRIEVE' ) group by entity , activity order by 8 desc failed.
    ANS8001I Return code 3.

    ANS8002I Highest return code was 3.

  9. #9
    Join Date
    Oct 2010
    Posts
    14

    the MB columns

    It ought to be one of these that is causing the problem:


    cast ( sum ( bytes ) /1024/1024 as decimal ( 10 , 0 ) ) as TOTAL_MB
    cast ( sum ( bytes ) / cast ( SECOND ( max ( end_time ) -min ( start_time )) as decimal ) as decimal ) /1024/1024 as MB_SEC

    What's the datatype for the BYTES column?

  10. #10
    Join Date
    Oct 2010
    Posts
    27
    Quote Originally Posted by Anand.Kaushal View Post
    It ought to be one of these that is causing the problem:


    cast ( sum ( bytes ) /1024/1024 as decimal ( 10 , 0 ) ) as TOTAL_MB
    cast ( sum ( bytes ) / cast ( SECOND ( max ( end_time ) -min ( start_time )) as decimal ) as decimal ) /1024/1024 as MB_SEC

    What's the datatype for the BYTES column?
    I believe it's a BIGINT.

  11. #11
    Join Date
    Oct 2010
    Posts
    14

    check the limits

    check the max value for sum(bytes).. based on that check if decimal(10,0) is appropriate.. you could also cast before or right after you sum, incase the summed value exceeds the limits of BIGINT.

  12. #12
    Join Date
    Oct 2010
    Posts
    27
    Quote Originally Posted by anand.kaushal View Post
    check the max value for sum(bytes).. Based on that check if decimal(10,0) is appropriate.. You could also cast before or right after you sum, incase the summed value exceeds the limits of bigint.
    tabschema: Tsmdb1
    tabname: Activity_summary
    colname: Bytes_affected
    colno: 9
    index_keyseq:
    Index_order:
    Typename: Bigint
    length: 8
    scale: 0
    nulls: True
    remarks:

  13. #13
    Join Date
    Oct 2010
    Posts
    14
    What does this return - just tell me the max_mb value from it

    select entity, activity, max(sum ( bytes )) max_MB
    from summary where ( start_time >= timestamp ( char ( ( date ( current_timestamp ) - ( 0+1 ) day ) , ISO ) || ' 18:00:00' ) and start_time <= timestamp ( char ( ( date (
    current_timestamp ) - ( 0 ) day ) , ISO ) || ' 06:00:00' ) ) and activity in ( 'BACKUP' , 'ARCHIVE' , 'RESTORE' , 'RETRIEVE' ) group by entity, activity order by 3 desc fetch first row only
    Last edited by Anand.Kaushal; 10-09-10 at 11:21.

  14. #14
    Join Date
    Oct 2010
    Posts
    27
    In TSM V6 you can't query the SUMMARY table with
    select * from columns where tabname='SUMMARY'

    I did it on V5 server and got this result for BYTES column

    tabschema: ADSM
    tabname: SUMMARY
    colname: BYTES
    colno: 12
    type: DECIMAL
    len: 18
    scale: 0
    nulls: TRUE
    remarks: Bytes processed

  15. #15
    Join Date
    Oct 2010
    Posts
    14

    in that case..

    try using decimal(20,0) for all casts in those MB columns

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •