how many objects are we talking about here? at some point running some sql will be much faster than the blcli command...
We have many objects... but what's a quick way of getting the total number on an BL app server?
If you have a better/more efficient/more compatible way of doing this via sql we'd love to see it.
You have an oracle db ?
You can query on Database on database side.
Dean, plan to upgrade you BL Environment, 7.4 is very old.
Siddu… this task is the beginning of our upgrade process.
As Bill mentioned, we would like to have a query that gets the number of objects.
Also we’d like to be able find out which depot objects have which dependencies in jobs or when the last time a depot object was used.
If you’d like to help us out that’d be outstanding!
siddu - dean did say they were upgrading - that's the point of this exercise - to figure out what to take over to the new environment.
dean try something like this:
select temp1.path,j.name,nvl(max(to_char(jr.start_time,'YYYY-MM-DD HH24:MI:SS')),0),o.name,temp2.path,do.name,o2.name,do.location from job j
left join object_type o on j.object_type_id = o.object_type_id
left join job_run jr on j.job_id = jr.job_id and j.job_version_id = jr.job_version_id
left join job_depot_object jdo on j.job_id = jdo.job_id and j.job_version_id = jdo.job_version_id
left join depot_object do on jdo.depot_object_id = do.depot_object_id and jdo.depot_object_version_id = do.depot_object_version_id
left join object_type o2 on do.object_type_id = o2.object_type_id
left join (select group_id, sys_connect_by_path( name, '-/-' ) path from blgroup start with parent_group_id = 0 connect by prior group_id = parent_group_id) temp2 on temp2.group_id = do.group_id
left join (select group_id, sys_connect_by_path( name, '-/-' ) path from blgroup start with parent_group_id = 0 connect by prior group_id = parent_group_id) temp1 on temp1.group_id = j.group_id
where j.is_deleted = 0 and j.is_latest_version = 1 and j.is_saved_explicitly = 1
and do.is_deleted = 0 and do.is_latest_version = 1 and do.is_saved_explicitly = 1
group by j.name,o.name,do.name,o2.name,do.location,temp1.path,temp2.path
order by nvl(max(to_char(jr.start_time,'YYYY-MM-DD HH24:MI:SS')),0) desc;
that will give you the job workspace path, job name, last job run, depot object path, name, type and file system location.
if you have sqldev or toad run it and you can export to a csv. otherwise we can add some formatting statements and you can run from sqlplus.
Thanks Bill. We started with this today... 90k results returned on one of our BL instances... we'll start parsing through it
Was that expected ? ☺ do some spot checks to make sure it’s accurate…