Shellscript for running DQL commands

Srnu
Srnu
in Documentum #1

Hi everyone,

I need to create a shellscript to run a few DQL commands on unix environment, but I dont know how to run/use DQL commands via shellscript. Any ideas on how I can do this?

(Example scenario: first run select query and then use result of that in update dql query in shell script)

Comments

  • DCTM_Guru
    DCTM_Guru
    #2

    If you create a DQL script file, you can pass the script file to IDQL command line utility. The syntax to call IDQL is in the Content Server Admin manual. As for the DQL query, you didn't provide enough details on what object type you are trying to query and/or update.

  • bacham
    bacham
    #3

    About your second requirement to use the result in an update statement, this is not possible with idql unless you can embed your select statement in your update statement somehow. If not, it would be better to use a scripting language which would use the DFC Java library (e.g. jython, groovy or jruby).

  • DCTM_Guru
    DCTM_Guru
    #4

    Or Excel :) We actually do a lot of select/updates using Excel and its functions.

  • Srnu
    Srnu
    #5

    Hi everyone,

    The query I am using is select count(*) from dm_user where user_name in (select arguments_values from dm_job_request where request_completed=1 and r_creation_date > DATE('09/01/2019 21:00:00','mm/dd/yyyy HH:MM:SS'))

    But the constraint here is I have take the previous date (yesterday's) date while running the script using cron job, so I am having trouble understanding how I can take date input from unix and then use that in the dql query????

  • bacham
    bacham
    #6

    @Srnu said:
    But the constraint here is I have take the previous date (yesterday's) date while running the script using cron job, so I am having trouble understanding how I can take date input from unix and then use that in the dql query????

    You can't: idql is not really suited for that. You would have to call idql for each DQL statement from a shell script, process the results (e.g. via sed or some other Unix utility), construct the next DQL statement and repeat. Very cumbersome and very slow. It's better to write your job in Java or use another scripting language like I wrote before.

    If your only variable is yesterday's date, you can get it via the DQL function DATE(YESTERDAY).

  • Srnu
    Srnu
    #7

    I have created a dql script file called input.dql with 2 dql queries, I want to call that file from shellscript; can you please help with the command for this?

  • DCTM_Guru
    DCTM_Guru
    #8

    Look at this link (same information in the Documentum Admin manual):

    https://bonson.wordpress.com/documentum/system-administration-using-api/

Categories