Backup Manager Functions Version 10.2.01P10

Pipelined Functions for Report Query Building > Backup Manager Functions

Version 10.2.01P10

Backup Manager Functions

Function	Description
APTgetJobTypeName	Used to query database tables related to backup job details, such as apt_v_job and apt_v_nbu_job
APTgetTapeDriveStatusName	Use this function to access data from: apt_v_tape_drive. This function returns a character string.
getFullPathname	Use this function to access data from: apt_v_client_file. This function returns a character string.
listJobSummaryAfterRestart	Returns a list of NetBackup jobs. It could be used, for example, to determine the ultimate success for NetBackup jobs within a backup window. For example, if the backup window was set for 4:00 p.m. to 4:00 p.m. the next day, if the restart completed before the end of the backup window, it is considered to be successful.
listJobSummaryAfterRestart for NetWorker Backup Jobs	Returns a list of NetWorker jobs that do not have a backup window. It could be used, for example, to determine ultimate success for NetWorker jobs. If the job restart finishes within the timeframe (startDate - finishDate), it is considered successful.
listJobSummaryAfterRestartNBW	Returns a list of NetBackup jobs that do not have a backup window. It could be used, for example, to determine ultimate success for NetBackup jobs.
listOfBackupWindowDates	Two versions of this function enable the following functionality: • returns a list of backup windows explicitly supplied in the function • returns a list of backup windows gleaned from the windows defined in the Portal
listOfBackupWindowDates (by Backup Window ID)	Based on the backup window definition, the function returns a list of the adjusted start and finish dates.

APTgetJobTypeName

This function can be used to query database tables related to backup job details, such as apt_v_job and apt_v_nbu_job.

FUNCTION APTgetJobTypeName(

productType,

jobType,

vendorJobType)

This function returns a character string.

Note: In the SQL Template Designer query, the function name must be prefaced with: rtd.

Argument Name	Type	Length
productType	Number	2
jobType	Number	4
vendorJobType	Number

Example Query 1

SELECT rtd.APTgetJobTypeName(j.product_type, j.job_type, NULL) FROM apt_v_job j

Example Query 2

SELECT rtd.APTgetJobTypeName(j.product_type, j.job_type, n.vendor_job_type)

FROM apt_v_nbu_job n, apt_v_Job j

WHERE j.job_id = n.job_id

The output from this example:

Full Backup

Appl Backup

APTgetTapeDriveStatusName

Use this function to access data from:

apt_v_tape_drive

This function returns a character string.

Note: In the SQL Template Designer query, the function name must be prefaced with: rtd.

FUNCTION APTgetTapeDriveStatusName(

vendorProductType,

vendorDriveStatus)

Argument Name	Type	Length
vendorProductType	Number
vendorDriveStatus	Character	1

Example Query

SELECT rtd.APTgetTapeDriveStatusName(d.product_type, d.vendor_drive_status) FROM apt_v_tape_drive d

The output from this example:

In-Use

Mounting

getFullPathname

Use this function to access data from:

apt_v_client_file

This function returns a character string.

Note: In the SQL Template Designer query, the function name must be prefaced with: rtd.

FUNCTION getFullPathname(

clientID,

clientFileID)

Argument Name	Type	Length
clientID	Number	6
clientFileID	Number	10

Example Query

SELECT rtd.getFullPathname(d.client_id, d.client_file_id) FROM apt_v_client_file d

listJobSummaryAfterRestart

This function returns a list of NetBackup jobs. It could be used, for example, to determine ultimate success for NetBackup jobs within a backup window. For example, if the backup window was set for 4:00 p.m. to 4:00 p.m. the next day, if the restart completed before the end of the backup window, it is considered to be successful.

Note: In the SQL Template Designer query, the function name must be prefaced with: rtd.

listJobSummaryAfterRestart(

startDate,

finishDate,

backupWindowId,

clientList,

excludeStatusList,

excludePolicyList,

excludeClientList)

Argument Name	Type
startDate	DATE with the value derived from the Scope Selector
finishDate	DATE with the value derived from the Scope Selector
backupWindowId	Numeric
clientList	numberListType Example: numberListType(1,2,3)
excludeStatusList	numberListType Example: numberListType(1,2,3)
excludePolicyList	numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2))
excludeClientList	numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2)))

Example Query

SELECT job_id, nbu_job_id, c.hostname client, s.hostname server, start_date,

finish_date, kilobytes, DECODE(overall_status,0,

'Successful',1,'Partial',2,'Queued', 3,'Running',4,'Failed', NULL) overall_status,

DECODE(was_restarted,0,'No','Yes') was_restarted, vendor_status, orig_vendor_status,

file_pathlist, window_start_date, window_finish_date

FROM TABLE(nbu_rtd.listJobSummaryAfterRestart(${startDate},${endDate},100000,${spHosts},

null,null,null)) t, apt_v_server s, apt_v_server c

WHERE t.server_id = s.server_id

AND t.client_id = c.server_id

ORDER BY t.start_date

listJobSummaryAfterRestartNBW

This function returns a list of NetBackup jobs regardless of a backup window. It could be used, for example, to determine ultimate success for NetBackup jobs. If the job restart finishes within the timeframe (startDate -finishDate), it is considered successful.

Note: In the SQL Template Designer query, the function name must be prefaced with: rtd.

listJobSummaryAfterRestartNBW

startDate,

finishDate,

clientList,

excludeStatusList,

excludePolicyList,

excludeClientList,

jobTypeList)

Argument Name	Type
startDate	DATE with the value derived from the Scope Selector
finishDate	DATE with the value derived from the Scope Selector
clientList	numberListType Example: numberListType(1,2,3)
excludeStatusList	numberListType Example: numberListType(1,2,3)
excludePolicyList	numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2))
excludeClientList	numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2)))
jobTypeList	numberListType Example: numberListType(1,2,3)

Example Query

SELECT job_id, nbu_job_id, c.hostname client, s.hostname server, start_date,

finish_date, kilobytes, DECODE(overall_status,0,

'Successful',1,'Partial',2,'Queued', 3,'Running',4,'Failed', NULL) overall_status,

DECODE(was_restarted,0,'No','Yes') was_restarted, vendor_status, orig_vendor_status,

file_pathlist, window_start_date, window_finish_date

FROM TABLE(nbu_rtd.listJobSummaryAfterRestartNBW(${startDate},${endDate},${spHosts},

null,null,null,null)) t, apt_v_server s, apt_v_server c

WHERE t.server_id = s.server_id

AND t.client_id = c.server_id

ORDER BY t.start_date

listJobSummaryAfterRestart for NetWorker Backup Jobs

This function returns a list of NetWorker jobs within a backup window. It could be used, for example, to determine ultimate client backup success for EMC NetWorker jobs. If the job restart finishes within the time frame (startDate - finishDate), it is considered successful.

Note: In a SQL Template Designer query, the function name must be prefaced with: leg_rtd.

listJobSummaryAfterRestart(

startDate,

finishDate,

backupWindowId,

clientList,

excludeStatusList,

excludePolicyList,

excludeClientList)

Argument Name	Type
startDate	DATE with the value derived from the Scope Selector
finishDate	DATE with the value derived from the Scope Selector
backupWindowId	If no backup window is defined, the default is 12 hours from the start date. To configure a Backup Window via the Portal: Admin > Reports > Backup Windows and click Add. To determine the backupWindowId, see listOfBackupWindowDates (by Backup Window ID).
clientList	numberListType Example: numberListType(1,2,3)
excludeStatusList	numberListType Example: numberListType(1,2,3)
excludePolicyList	numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2))
excludeClientList	numberObjectListType Example: numberObjectListType(numberObjectType(1), numberObjectType(2)))

Example Query

NOTE: In the following sample Report Template query, spHosts is the same as Hosts, but is specifically for use in stored procedures.

SELECT job_id, c.hostname client, s.hostname server, sc.schedule_name, start_date, finish_date, kilobytes, DECODE(overall_status,0, 'Successful',1,'Partial',3,'Running',4,'Failed', NULL) overall_status,

DECODE(was_restarted,0,'No','Yes') was_restarted, vendor_status, orig_vendor_status, client_resource_name, window_start_date, window_finish_date

FROM TABLE(leg_rtd.listJobSummaryAfterRestart(${startDate},${endDate},100000,${spHosts},
null,null,null)) t, apt_v_server s, apt_v_server c, apt_v_leg_schedule sc

WHERE t.server_id = s.server_id

AND t.client_id = c.server_id

AND t.schedule_id = sc.schedule_id(+) ORDER BY t.start_date

listOfBackupWindowDates

Two versions of this function enable the following functionality:

• returns a list of backup windows explicitly supplied in the function

• returns a list of backup windows gleaned from the windows defined in the Portal - See listOfBackupWindowDates (by Backup Window ID)

Note: In the SQL Template Designer query, the function name must be prefaced with: rtd.

Explicitly Supply the Backup Windows

FUNCTION listOfBackupWindowDates(

startDate,

finishDate,

backupWindowList IN apt_BackupWindowListType)

Argument Name	Type
startDate	Date
finishDate	Date
backupWindowList

Based on the backup window definition, the above function returns a list of the adjusted start and finish dates.

Example Query

SELECT start_date, finish_date

FROM TABLE(rtd.LISTOFBACKUPWINDOWDATES(TO_DATE('01012008','MMDDYYYY'), to_DATE('01072008','MMDDYYYY'),

apt_BackupWindowListType(

APT_BACKUPWINDOWTYPE('Mon', 40, 64),

APT_BACKUPWINDOWTYPE('Tue', 64, 88),

APT_BACKUPWINDOWTYPE('Wed', 88, 112),

APT_BACKUPWINDOWTYPE('Thu', 112, 136),

APT_BACKUPWINDOWTYPE('Fri', 136, 160),

APT_BACKUPWINDOWTYPE('Sat', 160, 184),

APT_BACKUPWINDOWTYPE('Sun', 16, 40)

)

))

The output from this example:

12/31/2007 4:00:00 PM 1/1/2008 3:59:59 PM

1/1/2008 4:00:00 PM 1/2/2008 3:59:59 PM

1/2/2008 4:00:00 PM 1/3/2008 3:59:59 PM

1/3/2008 4:00:00 PM 1/4/2008 3:59:59 PM

1/4/2008 4:00:00 PM 1/5/2008 3:59:59 PM

1/5/2008 4:00:00 PM 1/6/2008 3:59:59 PM

1/6/2008 4:00:00 PM 1/7/2008 3:59:59 PM

listOfBackupWindowDates (by Backup Window ID)

Retrieve Backup Windows (Defined in the Portal) with a Backup Window ID

FUNCTION listOfBackupWindowDates(

startDate,

finishDate,

windowGroupID IN apt_date_window.windows_group_id%TYPE)

Argument Name	Type
startDate	Date
finishDate	Date
windowGroupID

Based on the backup window definition, the above function returns a list of the adjusted start and finish dates.

Example Query

The following examples provide a basic idea of the function. These queries should serve as only an example of how the function might be incorporated into a more sophisticated query.

In a SQL Template Designer query, first determine the Backup Window IDs:

select window_group_id, window_group_name from apt_v_date_window

Then, use the Backup Window ID (window_group_id value from above example) in the following query:

select * from TABLE(rtd.listOfBackupWindowDates(TO_DATE('01012010','MMDDYYYY'), to_DATE('01072010','MMDDYYYY'), 100011 ))