I am planning to embed a cursor in one of my cobol programs.

Usually when we "open" the cursor, would the view/result set stored in the system memory/program memory or it would actually hit the DB2 everytime we fetch?

Is it better to use embedded cursors to handle data extraction or cobol itself?

I can't speak much for AS400, but usually cursors are the only way to retrieve data from an RDBMS. So you usually don't have any choice. AS400 may be different because you could possibly also access the data with file i/o operations.

Generally spoken, DB2 uses blocked cursors if possible, i.e. multiple rows are retrieved and send from the server to the client together. Thus, no new network round trip is incurred on each fetch - only when the rows cached at the client are exhausted. However, if you have a FOR UPDATE cursor, DB2 can't do that easily because the server has to know which row you want to update, i.e. it has to know the status. One approach chosen by DB2 LUW is, for example, to avoid block fetch in such cases.

my case has no update involved. So, is it advisable to still use Cursor? yup, we can do direct I/O to the file too, but the coding is going to be longer, in Cobol.

Which other options do you have? As I said: through SQL, you only have cursors to access the data (apart from SELECT INTO etc.) So if you don't want to use direct I/O, I don't see how this question could arise in the first place.