Neil:
Thanks for the reply I'm still having lock issues on the control file. My code is below as you can see I added a READU and a RELEASE not sure why it is not working.
OPEN 'CONTROL' TO F.CONTROL ELSE CONTROL.OPEN = 0
OPEN 'EPWCOMNINVMSPF' TO F.EPW ELSE STOP
*
* pull in the last date
*
READU DATEREC FROM F.CONTROL, 'LAST.DATE.REC' THEN
LAST.DATE = DATEREC<1>
END ELSE
LAST.DATE = ""
END
THIS.TIME = TIME()
*
* if running this for the first time today, create a save list in NI order
*
IF LAST.DATE # DATE() THEN
DATEREC<1> = DATE()
DATEREC<2> = THIS.TIME
DATEREC<3> = ""
DATEREC<4> = ""
WRITE DATEREC ON F.CONTROL,'LAST.DATE.REC'
RELEASE F.CONTROL,'LAST.DATE.REC'
Below is the lock that is set
Active Record Locks:
Device.... Inode.... Netnode Userno Lmode Pid Login Id Item-ID........
64771 139239659 0 -6 37 RU 22294 root LAST.DATE.REC
Below is the calling python program
conn = pyodbc.connect('DSN=dsn;UID=user;PWD=pass')
cursor = conn.cursor()
stmt = "CALL SQL_CALL_LIMIT_COMN LIMIT 10000"
Thanks JR Moore
------------------------------
JR Moore
IT Special Projects
Rocket Forum Shared Account
Hillsboro US
------------------------------
Original Message:
Sent: 12-23-2021 16:30
From: Neil Morris
Subject: SQL ODBC TO UNIVERSE LIMIT ROWS
Does the program perform a READU on F.CONTROL,'RECORD' before doing the WRITE? It would appear the WRITE is happening within a transaction and it expects a lock to be set on the record prior to the WRITE operation. The error indicates a lock is not set.
------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
Original Message:
Sent: 12-23-2021 14:30
From: JR Moore
Subject: SQL ODBC TO UNIVERSE LIMIT ROWS
Jeremy:
Thanks for the reply! When I retrieve the records I'm updating a control record and putting a date in the record that I sent the record. Then I select the records without the date from a sorted savedlist. Now I'm getting another error when calling the program.
Program "SQL_CALL_LIMIT_COMN": Line 26, FATAL: the locks necessary for database operations at the current isolation level (1) are not held by this process.
line26 = " WRITE DATE() ON F.CONTROL,'RECORD'
It seems to be a permissions issue with the ODBC user. If I run the same CALL in the ODBC tester with the same credentials used in my external Python program the CALL works?
------------------------------
JR Moore
IT Special Projects
Rocket Forum Shared Account
Hillsboro US
Original Message:
Sent: 12-22-2021 18:38
From: Jeremy Lockwood
Subject: SQL ODBC TO UNIVERSE LIMIT ROWS
After reading through this thread, I'm guessing that you don't always want just the first 10K records, you are looking to 'chunk' the file.
To achieve that, you'll also need an offset/skip.
I'm not sure about Universe (couldn't find the reference guide to check), but this isn't directly supported in AQL on D3. However; if you save the results to a list first, you can do the following:
execute "sselect file";execute "save-list listname";*first 10Kexecute "get-list listname (1-10000)"*next 10Kexecute "get-list listname (10001-20000)"
The overhead of having to select and save the list beforehand is not optimal, but I'm not aware of any ... SAMPLING {N} SKIP {N} - which would be a really nice addition.
Hope this helps.
Jeremy
------------------------------
Jeremy Lockwood
Awesome
ASE Supply Inc
Portland OR US
Original Message:
Sent: 12-17-2021 11:40
From: JR Moore
Subject: SQL ODBC TO UNIVERSE LIMIT ROWS
Can someone tell me the proper syntax for SQL statement to Universe. I have a large file that I need to fetch 10000 rows at a time from Universe that will update a remote AZURE DB daily. I don't have control over the AZURE site as EDA seems like a much better solution to keep the remote data current.
I have tried:
SELECT TOP 10000 * FROM FILE;
SELECT * FROM FILE LIMIT 10000;
SELECT * FROM FILE FETCH FIRST 10000;
SELECT * FROM FILE WHERE ROWNUM < 10000;
Thanks JR Moore
------------------------------
JR Moore
IT Special Projects
Rocket Forum Shared Account
Hillsboro US
------------------------------