0

I have a strange case here. I created a View in AS/400. I need to have view not join logical because I need to do a UNION ALL. The view is created and I went to IFS and granted permissions ALL. But when I open Crystal Reports, make the connection to iSeries, and go to that library of the View, it is not showing, so I cannot use.

Is there anything else I need to do?

I just tried something else. this is the view:

CREATE VIEW MKLIB/BEMPLOCM AS
   ((SELECT  LMRIDC, LMCOM#, LMWHS#, LMLOC1, LMLOC2, LMLOC3, LMLTPC, LMLCT1
     FROM
        ((SELECT  LMRIDC, LMCOM#, LMWHS#, LMLOC1, LMLOC2, LMLOC3, LMLTPC, LMLCT1
          FROM ASTDTA/ICLOCMLM WHERE LMLTPC IN ('PCK', 'PAL', 'RAK')) t1
     EXCEPTION JOIN
        (SELECT * FROM ASTDTA/ICBALMIE) t2
           ON LMLOC1=IELOC1 AND LMLOC2=IELOC2 AND LMLOC3=IELOC3 )
     EXCEPTION JOIN
        (SELECT * FROM ASTDTA/ICBLDTIR) t3
           ON LMLOC1=IRLOC1 AND LMLOC2=IRLOC2 AND LMLOC3=IRLOC3 ))

Now in Crystal reports there is also COMMAND to use to get your data, there you can make the same query but although I want it on the AS/400 for time consideration, the union is needed otherwise i have to run the queries via CL all day. SO I took the above code from the SELECT point, and got this error:

failed to retrieve data from the database. Details: HY000 IBM ISeries ACCESS ODBC DRIVER (DB2 UDB) sql 5016 - Qualified object name ICLOCMLM not valid. Vendor code 5016.

Not sure what that means.

Gilles 'SO- stop being evil'
  • 104,111
  • 38
  • 209
  • 254
Booksman
  • 1,565
  • 5
  • 19
  • 32
  • What does the **IFS** have to do with permissions of a **VIEW**? – James Allman Jul 10 '13 at 15:24
  • I thought that might be a reason why this view does not appear in list of objects to use in Crystal. in IFS before it did not have ALL for permissions so I changed it. – Booksman Jul 10 '13 at 15:32
  • Normally, when people write IFS, they mean a stream file, not an object in the QSYS.LIB file system. That's why James is asking what the IFS has to do with anything. You can't create a view in a stream file system. – Buck Calabro Jul 10 '13 at 17:13
  • OK I wrote a view, i want to use this view in crystal reports but it does not list it in the library when i see the tables and views in the crystal. My question is, what is the reason it's not being seen? – Booksman Jul 10 '13 at 17:56
  • VIew is written on the 400 via the STRSQL. On 400 I can run a query against this. WHat must I do to have the crystal reports see this view? – Booksman Jul 10 '13 at 19:07

3 Answers3

1

Use the GRANT statement to control SQL privileges.

If the view is over regular files you may also have to use the commands GRTOBJAUT, EDTOBJAUT and RVKOBJAUT to modify authorization on those objects.

James Allman
  • 40,573
  • 11
  • 57
  • 70
  • I did this: GRTOBJAUT OBJ(ASTCCDTA/BEMPLOCM) OBJTYPE(*ALL) USER(*PUBLIC) AUT(*ALL) is more needed? It still doesn't appear. – Booksman Jul 10 '13 at 15:57
  • Here, you are granting authority to BEMPLOCM in library ASTCCDTA. In the question, you created the view BEMPLOCM in library MKLIB. – Buck Calabro Jul 10 '13 at 17:12
  • its same issue my lib or that lib. is grant the way I did it sufficient? – Booksman Jul 10 '13 at 17:25
  • What library are you using in Crystal Reports? – Buck Calabro Jul 11 '13 at 02:02
  • the libs there are only 2 that are displayed when you open a ODBC connect to the I. My view is def. created in 1 of these 2. I spoke with IBM on this, and they feel that I must contact SAP SUpport for this i am in the process of that. – Booksman Jul 16 '13 at 13:39
1

"It does not list it in the library when I see the tables and views in crystal."

Are you sure that the view is actually in the library you think?

What may have happened is that it may have been created in another library. Try checking in QGPL library, or if you have a library that matches your user profile name, check there.

WarrenT
  • 4,502
  • 19
  • 27
0

If the question is 'Why do I get Qualified object name ICLOCMLM not valid.' the answer is probably that you are using *SQL naming and the statement you are running is using *SYSTEM naming. Try changing FROM ASTDTA/ICLOCMLM to FROM ASTDTA.ICLOCMLM and see if the 5016 error goes away.

Buck Calabro
  • 7,558
  • 22
  • 25
  • I know what the issue is now. Crystal only recognizes DDS created tables or Logicals. Because when I created a temp file via Query it does not see it, but then i copied that to a DDS created clone and it does see that. – Booksman Jul 17 '13 at 14:12
  • It works for me. RUNQRY QRY(*NONE) QRYFILE((MASTERFILE)) OUTTYPE(*OUTFILE) RCDSLT(*YES) OUTFILE(BUCK/BUCKMST *FIRST *NEWFILE) Then I start Crystal, Report Wizard, My Connections, MYIBMI, MYDBNAME, BUCK, Tables. I can see BUCKMST in the list of tables. And I can make a report with the columns in it. V7.1, Crystal 14.0.2.364. – Buck Calabro Jul 17 '13 at 14:28
  • Is your 'temp file' in QTEMP? Because there isn't a single library on the system called QTEMP; there's a separate one for every job. The QTEMP you see in Crystal is not the same QTEMP you see in an interactive job. – Buck Calabro Jul 17 '13 at 14:30
  • nope not qtemp. The thing is, only 2 libraries from the entire system show in the crystal list. WHile we have tens of libs. so i naturally create anything in one of the 2. – Booksman Jul 17 '13 at 22:40
  • Only 2 show... Could be your authority or more probably, the library list in the ODBC driver setup. You've been talking about a view, but files created by RUNSQL are tables (physical files). Can you edit your question (don't erase, but add to it) to describe exactly the steps you're taking? – Buck Calabro Jul 18 '13 at 13:12
  • I will check the odbc driver setup, this may be it. Deffo the issue is with Non DDS Logical or views. – Booksman Jul 22 '13 at 20:44