Pipelined Functions for Report Query Building > Backup Manager Functions
  
Version 10.2.01P10
Backup Manager Functions
Function
Description
Used to query database tables related to backup job details, such as apt_v_job and apt_v_nbu_job
Use this function to access data from: apt_v_tape_drive. This function returns a character string.
Use this function to access data from: apt_v_client_file. This function returns a character string.
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.
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.
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.
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
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
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:
Up
In-Use
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 ))